T-SQL replace function missing some occurrences with nvarchar(max)

Question

Given the following T-SQL code:

declare @lf char(1) set @lf = char(10);
declare @cr char(1) set @cr = char(13);

select  replace(isnull(note,''), @cr+@lf,@lf)  from T

are there circumstances where not every occurrence of @cr+@lf in the note column would be replaced with @lf?

I am trying to troubleshoot a situation where that is indeed happening.

The note column is defined as nvarchar(max). The documentation says:

If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type.

If I understand that correctly, there's no need to cast because note is already of the proper datatype to allow for return values greater than 8000 bytes.

I thought maybe the isnull function was not returning nvarchar(max) but the documentation says it returns the type of the value being tested:

... Return Types
Returns the same type as check_expression.

And the returned value isn't being truncated; it's just that some crlf pairs are being overlooked.

I must be overlooking something.

declare @t table( notes nvarchar(max));

insert @t(notes)
values
(
'From: kkkkkkk@aaaaaaaaa.com <kkkkkkk@aaaaaaaaa.com> 
Sent: Monday, May 00, 0008 00:55 PM
To: Jan Zzzz <sZzzz@dddd.dd.com>
Subject: RE: [Secure Message] aaaaaaaaa ABC ddddddddddddd--XXXXX-X

Hi Jan, 

The ddddddddddddd is valid for one year.  I have attached the Zzzzzzz Rrrrrrrr which you will find behind the  blank cover page and ddddddddddddd form.  Please let me know if this is what you need.  

Best Regards, 

Yyyyyy 

Kkkkkkkk Kkkkkk, ABC, DEF
ABC Mmmmmmmm
P 800.007.0000 ext 000 | F 600.000.0070 



Electronic mail is not considered to be a secure form of communication for Protected Health Information. If you are concerned about privacy, please call aaaaaaaaa directly at 0-800-007-0000. If you would like to review our privacy policy, it can be found on our website: www.ddddddddddddd.com.

This email, including any attached files, may contain confidential and privileged information for the sole use of the intended recipient(s). Any review, use, distribution or disclosure by others is strictly prohibited. If you are not the addressee indicated in this message (or authorized to receive information for the recipient), please contact the sender by reply e-mail and delete all copies of this message (including any attachments).

From: Jan Zzzz <sZzzz@dddd.dd.com> 
Sent: Monday, May 00, 0008 8:56 AM
To: Kkkkkkkk Kkkkkk <kkkkkkk@aaaaaaaaa.com>; Jan Zzzz <sZzzz@dddd.dd.com>
Subject: Re: [Secure Message] aaaaaaaaa ABC ddddddddddddd--XXXXX-X

Hi, this expired, I need a copy of the aaaaaaa aaaa so I can submit my aaaaaaa aaa aaaaaaaa. Thank you. SZzzz

On 0/00/0008 8:00 AM, Jan Zzzz wrote:
Thank you for the dddddddd, I am mmmmmmm mmm today.

On 0/0/0008 6:05 PM, Kkkkkkkk Kkkkkk wrote:

[Secure Message] aaaaaaaaa ABC ddddddddddddd--XXXXX-X    

Kkkkkkkk Kkkkkk has sent you a secure message.
Subject:    aaaaaaaaa ABC ddddddddddddd--XXXXX-X
From:   Kkkkkkkk Kkkkkk <kkkkkkk@aaaaaaaaa.com>

To: Jan Zzzz <sZzzz@dddd.dd.com>

Expires:    May 00, 0008

View Secure Message 



Note: If you have any concerns about the authenticity of this message please contact the original sender directly.'   
)

select notes from @t;
select replace(notes, char(13),'') from @t;

Answer 1

I believe I may have found a partial answer. In SSMS:

Tools->Options->SQL Server->Results to Grid

[ x ]  Retain CR/LF on copy or save

will actually restore the CR that your call to replace() has removed.

Answer 2

if its just a single line, this should do it, remove them all and put it back

set @note =  replace(replace(isnull(@note,''),@cr,''),@lf,'')+@lf . //or whatever line endings you want

if its multi line try something like this

declare @note as nvarchar(max)
declare @lf char(1) set @lf = char(10);
declare @cr char(1) set @cr = char(13);

set @note = 'A'+char(10)+char(13)+char(10)+char(13)+char(10)+char(13)+char(10)+char(13)+'A'+char(10)+char(13)

set @note = replace(isnull(@note,''),@cr,'')

--not sure if you want to keep all the user lf's but if you want only one try this?
if (patindex(isnull(@note,''),@lf+@lf) >= 0)
begin
set @note = replace(isnull(@note,''),@lf+@lf,@lf)
end 

select @note
select cast(@note as VARBINARY(100))
select len(@note)

Answer 3

Every circumstance will be replaced, but you might be creating some CrLfs via your replace. Please see the example below and how to mitigate it.

DECLARE @Cr CHAR(1)=CHAR(13)
DECLARE @Lf CHAR(1)=CHAR(10)
DECLARE @CrLf CHAR(2)=CHAR(13)+CHAR(10)
DECLARE @NoteTbl TABLE(Note NVARCHAR(MAX))
INSERT INTO @NoteTbl (Note) SELECT @Cr + @CrLf

--example can result in CrLF being created
SELECT [NewNote],LEN([NewNote]) FROM (SELECT replace(isnull(note,''), @CrLf,@lf) AS [NewNote] FROM @NoteTbl) AS a

--Option 1: Replace all Cr with nothing; this is effectively the same as replacing CrLf with Lf
SELECT [NewNote],LEN([NewNote]) FROM (SELECT replace(isnull(note,''), @Cr,'') AS [NewNote] FROM @NoteTbl) AS a

--Option 2: insert the notes into a table and loop until CrLf is gone, this might be useful if you need to do multiple different  data scrubs
DECLARE @NotesCleaned TABLE(Note NVARCHAR(MAX))
INSERT INTO @NotesCleaned (Note) SELECT Note FROM @NoteTbl
WHILE EXISTS(
    SELECT * FROM @NotesCleaned WHERE Note Like '%' + @CrLf + '%'
)
    BEGIN
        UPDATE @NotesCleaned SET Note=replace(isnull(note,''), @CrLf,@lf)
    END

SELECT Note,LEN(Note) FROM @NotesCleaned 

Answer 4

You would be left with @cr+@lf in the SELECT if @cr+@cr+@lf occurs in the note, unless you need @cr when it occurs on its own you are probably better off doing:

declare @cr char(1) set @cr = char(13);

select  replace(isnull(note,''), @cr,'')  from T