Remove Carriage Return for NULL values in column

  • SQL Server

    Old Hand

    Points: 336

    Folks:

    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

    SSChampion

    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)

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

    SSCoach

    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