SQL Server Performance Forum – Threads Archive
Carriage return in a column
Hi Gurus A column with ntext datatype sometimes has carriage return. This has to be removed, can you Please help me. rajconvert to nvarchar then use replace select replace(convert(nvarchar(4000), col1), char(13), ”)
KH
Don’t forget LF’s… char(10). <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />
I suppose you should use this – REPLACE(REPLACE(col1, NCHAR(13), ”), NCHAR(10), ”) – since a CRLF is two characters (with #13 before #10). The embedded REPLACEs will cover both a CR and a LF, which can occur isolated, and also a CRLF in case you have a ‘hard return’. I must say we’ve had some funny stuff with REPLACE on unicode strings, so it won’t hurt to test things out.
>>REPLACE(REPLACE(col1, NCHAR(13), ”), NCHAR(10), ”) I hope you meant it as REPLACE(REPLACE(cast(col1 as varchar(8000)), NCHAR(13), ”), NCHAR(10), ”)
Madhivanan Failing to plan is Planning to fail
Madhivanan, thank you for pointing out that it was NTEXT – if I remember correctly, REPLACE can have problems with TEXT and NTEXT columns. However, if you cast to VARCHAR then the whole expression will drop all non-ASCII characters from the column.
Well. How about using Substring function that returns nVarchar if the column is of ntext datatype? REPLACE(REPLACE(substring(col1,1,8000), NCHAR(13), ”), NCHAR(10), ”)
Madhivanan Failing to plan is Planning to fail
NVARCHAR strings can only be 4000 characters long. Anyway, BOL says REPLACE should handle TEXT and NTEXT, so there should be no problem.
>>Anyway, BOL says REPLACE should handle TEXT and NTEXT, so there should be no problem. No From BOL Syntax
REPLACE ( ‘string_expression1’ , ‘string_expression2’ , ‘string_expression3’ ) Arguments
‘string_expression1’ Is the string expression to be searched. string_expression1 can be of character or binary data. eg of mine declare @t table(data ntext)
insert into @t
select ‘test’
select replace(data,’T’,’K’) from @t
Madhivanan Failing to plan is Planning to fail
Well, it doesn’t say that REPLACE fails on text and ntext columns – but you’re right: it does fail. Poor detail in the documentation. Okay, you’ll have to use UPDATETEXT – see BOL, just follow the syntax carefully. It’s kind of hard-core programming, but it shouldn’t be too difficult.
]]>