title: T-SQL syntax to remove CR LF only on END of text string
body: <p>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</p>
  
  <pre><code>declare @NewLine char(2) 
  set @NewLine=char(13)+char(10)
  update tableName
    set columnName  =Replace(columnName  , @NewLine,'')
  where ( RIGHT(columnName ,2)=@NewLine)
  </code></pre>
  
  <p>Some strings have multiple paragraphs so the CR LFs are ok, just not on the end of the text string.</p>
  
  <p>The above code still replaces the CR LF throughout the entire string. </p>
  
  <p>SQL Server 2008 R2 Express </p>
  
  <p><strong>UPDATE:</strong>
  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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I am probably doing something wrong jsut not sure what else to try at this point.</p>