Remove Carriage Return for NULL values in column

  • SQL Server

    Old Hand

    Points: 336


    I would like to replace CHAR(13) (Carriage Return) with space and I use this SQL to replace it, but the problem is when the column has NULL values it would not replace it.  How can I remove carriage return from null values?

    UPDATE <Table Name>
    SET <Column Name> = REPLACE(Column Name, CHAR(13), '')

    • This topic was modified 4 days, 23 hours ago by  SQL Server.
  • frederico_fonseca


    Points: 14172


    there may be a misunderstanding of what you consider a NULL value on the column - if a Column is NULL then there is no char to replace at all so obviously it will not do the replacement.

    However if you are considering that char(0) is your null value then it definitely works - but how you check is not by "displaying" it on ssms but by converting the string to hex and looking at the result.

    See example below

    select t.str
    , replace(t.str, char(13), '') as str_1
    , convert(varbinary(10), t.str) as hex_1
    , convert(varbinary(10), replace(t.str, char(13), '')) as hex_1

    from (values('abc' + char(13) + char(0) + 'endstr')) t (str)

    str str_1 hex_1 hex_1
    abc abc 0x6162630D00656E647374 0x61626300656E64737472
  • Jonathan AC Roberts


    Points: 16879

    A new line can be made of carriage-return or linefeed characters or both. So really you should be replacing both.

    Try this:

    REPLACE(REPLACE(REPLACE(ColumnName, CHAR(13)+CHAR(10), ' '), CHAR(10), ' '), CHAR(13), ' ')

Viewing 3 posts - 1 through 3 (of 3 total)

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