0

How can I perform a replace query only on the end of a string where a hidden CRLF are present? Something like this but only at the end of the string not anywhere within the string

declare @NewLine char(2) 
set @NewLine=char(13)+char(10)
update tableName
  set columnName  =Replace(columnName  , @NewLine,'')
where ( RIGHT(columnName ,2)=@NewLine)

Some strings have multiple paragraphs so the CR LFs are ok, just not on the end of the text string.

The above code still replaces the CR LF throughout the entire string.

SQL Server 2008 R2 Express

UPDATE: My most recent testing of the suggested answers has revealed that in SSMS the CRLFs appear to remain in the mid string, but after copying content to Excel and running an ACSII compare on each character there are no CRLFs they appear to have been converted to spaces. I have been testing with this code and have exported results to a text file and CSV.

create table #t(t varchar(400))
insert into #t values('abc'+char(13)+char(10)+'xyz'+char(13)+char(10))
insert into #t values('rrrrr'+char(13)+char(10)+'sssss'+char(13)+char(10))
insert into #t values('Sentence1

Sentence2!

Sentence3
')
insert into #t values('rrrrr'+char(13)+char(10))
insert into #t values('Sentence111

Sentence222
')
insert into #t values('abc1234'+char(13)+char(10)+char(13)+char(10)+'xyz987'+char(13)+char(10)+char(13)+char(10)+'lmnop345'+char(13)+char(10))

declare @NewLine char(2) 
set @NewLine=char(13)+char(10)

select len(t),t from #t

update #t
set t = STUFF(t, LEN(t)-1, 2,'')
   where ( RIGHT(t ,2)=@NewLine)

select len(t),t from #t

drop table #t

I am probably doing something wrong jsut not sure what else to try at this point.

htm11h
  • 1,739
  • 8
  • 47
  • 104

2 Answers2

5

Don't use REPLACE, use STUFF

declare @NewLine char(2) 
set @NewLine=char(13)+char(10)
update tableName
set columnName = STUFF(columnName, LEN(columnName)-1, 2,'')
where ( RIGHT(columnName ,2)=@NewLine)
Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136
  • Same issue as above suggested answer..I don't believe this code is working. It appears to leave the CRLF in the mid string while removing the CRLF from the end of string, but an ASCII character comparison reveals that they have been removed and changed to spaces. – htm11h Jul 31 '13 at 16:55
  • Something else is going on then. STUFF replaces characters at single dedicated position only. Are u sure they're missing in the middle? Are u sure nothing else is happening in between? – Yuriy Galanter Jul 31 '13 at 16:57
  • You can't simple copy/paste output of SELECT - it will replace the CrLf. Trust SSMS in that, and if you need any further proof - use `print` to output individual values as debug/test – Yuriy Galanter Jul 31 '13 at 17:53
1

REPLACE isn't the way to go on this. You have several options including STUFF, SUBSTRING and LEFT. The implementation for LEFT is as follows:

declare @NewLine char(2) 
set @NewLine=char(13)+char(10)
update tableName
  set columnName  = LEFT(columnName, LEN(columnName) - 1)
where ( RIGHT(columnName ,2)=@NewLine)

Hope this helps.

rhoadsce
  • 278
  • 2
  • 6
  • I don't believe this code is working. It appears to leave the CRLF in the mid string while removing the CRLF from the end of string, but an ASCII character comparison reveals that they have been removed and changed to spaces. – htm11h Jul 31 '13 at 16:55
  • If you're pasting the results into Excel (given you Update to your original post), it could be Excel manipulating the string during the paste process. I have seen that happen before. You can use "CHARINDEX" to search the results for the remaining CRLF. – rhoadsce Aug 01 '13 at 17:22