Post reply

how to remove carriage return symbol?

  • November 1, 2006 at 8:45 am

    #367777

    Hello All!I have a very small question but can't figure out how to deal with it

    I am importing the set of rows into the txt file to transfer the data to another system, but if any field has carriage return sympbol inside, the row gets splitted into two lines and another system not processing the file properly.

    Any chance to filter/convert the field to remove any return characters?

    Any ideas much much appreciated!!!

  • November 1, 2006 at 9:05 am

    #669235

    Just for clarification, the carriage return is in the data that you are exporting to a text file?

    If that is the case, you could probably do this:

    SELECT REPLACE(REPLACE(Column,CHAR(10),''),CHAR(13),'') as Column

    FROM...

    SQL guy and Houston Magician

  • November 1, 2006 at 10:32 am

    #669265

    I tried that....

    Server: Msg 8116, Level 16, State 1, Line 1

    Argument data type text is invalid for argument 1 of replace function.

    Doesn't work

  • November 1, 2006 at 10:41 am

    #669268

    ooops I've got it. Converted Text to varchar.

    Thanks a lot!

  • November 1, 2006 at 11:10 am

    #669273

    Ah, it's a text column.

    I'm sure you know this but I want to double check. Are you running sql 2005 (and converting to VARCHAR(max)? If you are not, you may lose some of your text because in SQL 2000, VARCHAR has a max length of 8000 bytes.

    SQL guy and Houston Magician

  • November 1, 2006 at 3:16 pm

    #669360

    I am running SQL 2000 but this field needs to be converted to varchar(50) before the transfer anyway because of another system limitations. Thanks a lot again

Viewing 6 posts - 1 through 5 (of 5 total)

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