Remove Carriage Return for NULL values in column

  • SQL Server

    Old Hand

    Points: 353

    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 6 months ago by  SQL Server.
  • frederico_fonseca

    SSChampion

    Points: 14497

     

    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: 17216

    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), ' ')
  • gvoshol 73146

    Hall of Fame

    Points: 3161

    Why the extra step of replacing the concatenated CR and LF?  Doesn't replacing the individual CHARs suffice?  That's how I've done it, and I'd like to know if there is something that I'm missing.

     

     

  • Eirikur Eiriksson

    SSC Guru

    Points: 182425

    SQL Server wrote:

    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), '')

    As Frederico has stated earlier, there must be some kind of misunderstanding here, a NULL is not a value but an entry in the NULL Bitmap for the row signifying that the column does not have a value, hence no operations are possible.

    😎

    Can you clarify by posting the DDL (create table) script and sample data as an insert statement please?

  • dmbaker

    SSCertifiable

    Points: 5040

    Why the extra step of replacing the concatenated CR and LF?  Doesn't replacing the individual CHARs suffice?  That's how I've done it, and I'd like to know if there is something that I'm missing.

    Replacing the individual characters would suffice, and might be better depending on the source of the file.

    Generally, the CR/LF characters are together, in a pair, where they signify an end of line (in a Windows file). So it's pretty common to search for them together ("concatenated") and replace them like that. Not all operating systems use CR/LF for end of line though...some use just LF (e.g. Linux) and some use just CR (e.g. Mac OS, I think?) Replacing the individual characters is perfectly fine...result is the same as replacing the two together (as long as the two are together, if you try to replace the concatenated pair in a Linux file you probably won't find them together, of course).

  • Eirikur Eiriksson

    SSC Guru

    Points: 182425

    dmbaker wrote:

    Why the extra step of replacing the concatenated CR and LF?  Doesn't replacing the individual CHARs suffice?  That's how I've done it, and I'd like to know if there is something that I'm missing.

    Replacing the individual characters would suffice, and might be better depending on the source of the file.

    Generally, the CR/LF characters are together, in a pair, where they signify an end of line (in a Windows file). So it's pretty common to search for them together ("concatenated") and replace them like that. Not all operating systems use CR/LF for end of line though...some use just LF (e.g. Linux) and some use just CR (e.g. Mac OS, I think?) Replacing the individual characters is perfectly fine...result is the same as replacing the two together (as long as the two are together, if you try to replace the concatenated pair in a Linux file you probably won't find them together, of course).

    dmbaker wrote:

    Why the extra step of replacing the concatenated CR and LF?  Doesn't replacing the individual CHARs suffice?  That's how I've done it, and I'd like to know if there is something that I'm missing.

    Replacing the individual characters would suffice, and might be better depending on the source of the file.

    Generally, the CR/LF characters are together, in a pair, where they signify an end of line (in a Windows file). So it's pretty common to search for them together ("concatenated") and replace them like that. Not all operating systems use CR/LF for end of line though...some use just LF (e.g. Linux) and some use just CR (e.g. Mac OS, I think?) Replacing the individual characters is perfectly fine...result is the same as replacing the two together (as long as the two are together, if you try to replace the concatenated pair in a Linux file you probably won't find them together, of course).

    You cannot replace anything within nothing, NULL is not a value so if the OP's assumption of the column being NULL then there is nothing to replace!

    😎

     

  • Jonathan AC Roberts

    SSCoach

    Points: 17216

    gvoshol 73146 wrote:

    Why the extra step of replacing the concatenated CR and LF?  Doesn't replacing the individual CHARs suffice?  That's how I've done it, and I'd like to know if there is something that I'm missing. 

    If you want to replace them with just one space then if you have CR and LF together then replacing them individually would replace the new line with two spaces.

  • dmbaker

    SSCertifiable

    Points: 5040

    Eirikur Eiriksson wrote:

    You cannot replace anything within nothing, NULL is not a value so if the OP's assumption of the column being NULL then there is nothing to replace!

    Yeah, duh? I wasn't referring to database NULL, nor (I think) was OP (I think). My comment had nothing to do with database NULL.

    And, at least with a string value, you can replace something with "nothing" (aka an "empty string"), which it seems was ultimately OP's intent. Confusion arose due to OP's use of "NULL", apparently referring to ASCII NUL (CHAR(0)), not database NULL, it looks like.

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

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