October 9, 2019 at 5:42 pm
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), '')
October 9, 2019 at 6:12 pm
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
strstr_1hex_1hex_1
abcabc0x6162630D00656E6473740x61626300656E64737472
October 9, 2019 at 7:57 pm
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), ' ')
October 15, 2019 at 12:37 pm
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.
October 15, 2019 at 12:51 pm
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?
October 15, 2019 at 1:39 pm
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).
October 15, 2019 at 1:47 pm
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).
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!
😎
October 15, 2019 at 4:15 pm
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.
October 16, 2019 at 12:49 pm
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply