Post reply

Removing Carriage Returns

  • August 28, 2009 at 1:53 pm

    #97981

    Good Afternoon,

    I have a couple of columns, data types nvarchar and text, with inbedded carriage returns in my .csv data extract. These are causing problems when loading the data as a text file and I need to figure out how to remove them. Any help is greatly appreciated.

    Thanks for your time.

    Costa,

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • August 28, 2009 at 4:44 pm

    #1046429

    Try:

    UPDATE tablename

    SET columnname= REPLACE(SUBSTRING(columnname, 1, DATALENGTH(columnname)), char(13), '')

    WHERE charindex(char(13) , columnname) > 0

    HTH!

    MJ

  • August 28, 2009 at 5:07 pm

    #1046432

    Is the data ending up in the right columns in the database table, or are the carriage returns causing the load to fail?

    If I was faced with this problem I'd probably use VB to read the file and remove the carriage returns that occur before the end of the text row. This would take a bit of thinking, but it's doable.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • August 28, 2009 at 7:58 pm

    #1046445

    MANU (8/28/2009)

    Try:

    UPDATE tablename

    SET columnname= REPLACE(SUBSTRING(columnname, 1, DATALENGTH(columnname)), char(13), '')

    WHERE charindex(char(13) , columnname) > 0

    HTH!

    MJ

    Manu,

    Just curious... Why are you using SUBSTRING in this? REPLACE should be able to do this on it's own. Is it to handle more than 8kb in association with the TEXT datatype?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 30, 2009 at 2:54 pm

    #1046667

    Jeff,

    The attempt is to just handle the text or nvarchar datatype.

    Thanks,

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • August 30, 2009 at 4:37 pm

    #1046680

    Heh... have you or Manu actually tried it on the TEXT datatype to see if it works?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 31, 2009 at 11:40 am

    #1047015

    I tried and it doesnt work. The extract still has inbedded carriage returns like before with attempts in some records to remove it.

    Thanks

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • August 31, 2009 at 12:17 pm

    #1047034

    Give this a whirl ...

    One of the three following statements or a combination of the two of these statements will work. This depends on whether there are 'newlines', 'carriage returns' olone or 'carriage return/line feeds':

    update tablename

    set columnname=replace(cast(cast(columnname AS text) AS varchar(max)),char(10),'')

    update tablename

    set columnname=replace(cast(cast(columnname AS text) AS varchar(max)),char(12),'')

    update tablename

    set columnname=replace(cast(cast(columnname AS text) AS varchar(max)),char(13),'')

    As for efficiency, well ...

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • August 31, 2009 at 10:17 pm

    #1047299

    Rudy,

    varchar(max) is in SQL 2005 so I dont think your code will work.

    MJ

  • September 1, 2009 at 9:12 am

    #1047609

    oops ... did in in 2005 - well varchar(8192) will work - then one has to walk through the text ...

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • September 2, 2009 at 7:38 am

    #1048124

    Hi

    Try this

    Update tablename set columnname = replace(replace(substring(columnname,1,datalength(columnname)), char(13),''), char(10), ' ')

  • September 2, 2009 at 9:59 am

    #1048266

    rudy komacsar (9/1/2009)

    oops ... did in in 2005 - well varchar(8192) will work - then one has to walk through the text ...

    Heh... maybe VARCHAR(8000) or SQL_Variant (fixed at 8016). 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 2, 2009 at 10:00 am

    #1048269

    vicky (9/2/2009)

    Hi

    Try this

    Update tablename set columnname = replace(replace(substring(columnname,1,datalength(columnname)), char(13),''), char(10), ' ')

    REPLACE doesn't work on the TEXT datatype, folks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 2, 2009 at 10:25 am

    #1048283

    The replace doesn't work directly to the column, with the substring the sentence works

    Create Table #tablename(columnname text)

    Insert Into #tablename

    Select 'Please post

    the contents '

    Select * From #tablename

    Update #tablename set columnname = replace(replace(substring(columnname,1,datalength(columnname)), char(13),''), char(10), ' ')

    Select * From #tablename

    Results:

    columnname

    ------------

    Please post

    the contents

    columnname

    ---------------------------

    Please post the contents

  • September 2, 2009 at 10:30 am

    #1048285

    I use a function and just add in the ASCII character of the item I wish to replace:-

    /*-- =============================================

    -- Author:Carolyn Richardson

    -- Create date: 16th February 2009

    -- Description:Clears selected characters from text string

    ASCII characters:-

    32 = space

    9 = tab

    10 = New Line

    13 = carrige return

    -- =============================================================================*/

    CREATE FUNCTION [dbo].[fn_ReplaceAsciiChar]

    (

    @OrigString VARCHAR(255)

    )

    RETURNS VARCHAR(255) WITH SCHEMABINDING

    AS BEGIN

    DECLARE @NewString VARCHAR(255),

    @Space INT,

    @Len INT,

    @Ctr AS INT,

    @Ctr2 AS INT,

    @Char AS VARCHAR(1)

    SET @NewString = @OrigString

    SET @Len = ( SELECT LEN(@NewString)

    )

    SELECT @Ctr2 = 1,

    @Ctr = 1

    WHILE @Ctr <= @Len

    BEGIN

    SELECT @Char = SUBSTRING(@NewString, @Ctr, 1)

    IF ASCII(@Char) IN ( 9,10,13,32 )

    BEGIN

    SET @NewString = ( SELECT REPLACE(@NewString, @Char, CHAR(32))

    )

    SELECT @Ctr2 = @Ctr2 + 1

    END

    SELECT @Ctr = @Ctr + 1

    CONTINUE

    END

    RETURN @NewString

    END

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply