none
Removing hidden CRLF only from end of text string

    Question

  • 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 strings not anywhere within the string....

    REPLACE(dbo.yourcolumn, CHAR(13) + CHAR(10),)

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

    Tuesday, July 30, 2013 3:52 PM

All replies

  • You can check whether there is a hidden CRLF at the end.

    Try something like this:

    CASE WHEN charindex(CHAR(13) + CHAR(10), REVERSE(yourcolumn))=1 THEN Replace(yourcolumn,CHAR(13) + CHAR(10),'') ELSE yourcolumn END

    Tuesday, July 30, 2013 4:32 PM
    Moderator
  • Actually, This does not work, it replaces all occurances of CR LF not just the last set on end of string. This issue is still open....I thought I figure this out with this block of code.....

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


    • Marked as answer by marc111h Tuesday, July 30, 2013 4:43 PM
    • Edited by marc111h Tuesday, July 30, 2013 5:34 PM Not yet solved
    • Unmarked as answer by marc111h Tuesday, July 30, 2013 5:34 PM
    Tuesday, July 30, 2013 4:43 PM
  • Actually, This does not work, it replaces all occurances of CR LF not just the last set on end of string. This issue is still open....I thought I figure this out with this block of code.....

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


    Even this code also removes all the occurrences, the only difference is it filters and updates the rows with CR LF characters in the end, try,

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

    As sample data was not provided I just took this example,

    declare @NewLine char(2) 
     set @NewLine=char(13)+char(10)
    
    
     declare @columnName varchar(100)='test'+char(13)+char(10)+'joke'+char(13)+char(10)
     
          set @columnName  =LEFT(@columnName  , len(@columnName)-2)
    	  select @columnName
    
    --or
    set @columnName='test'+char(13)+char(10)+'joke'+char(13)+char(10)
          set @columnName  =STUFF(@columnName,len(@columnName)-1,2,'')
    
    	  select @columnName
    

     

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, July 31, 2013 12:58 AM
  • I am not sure I understand your response. I must NOT remove ALL CR LFs, only the CRLF on end of string.

    If you save the results to a text file and open the text file you will find a blank line after the ending text. This is what needs to be removed.


    • Edited by marc111h Wednesday, July 31, 2013 1:49 PM
    Wednesday, July 31, 2013 1:43 PM
  • Try this:

    create table #t(t varchar(100))
    insert into #t values('abc'+char(13)+char(10))
    insert into #t values('defgh')
    
    UPDATE #t
    SET t = SUBSTRING(t, 1, LEN(t)-2 )
    WHERE t LIKE '%'+CHAR(13)+CHAR(10)
    
    select len(t),t
    from #t
    
    drop table #t
    



    Gert-Jan

    Wednesday, July 31, 2013 1:51 PM
  • Thanks for the input Gert-Jan, but still no luck. I added to the sample data to more acurately reflect the real data.

    create table #t(t varchar(100))
    insert into #t values('abc'+char(13)+char(10)+'xyz'+char(13)+char(10))
    insert into #t values('defgh')
    insert into #t values('abc1234'+char(13)+char(10)+'xyz987'+char(13)+char(10)+'lmnop345'+char(13)+char(10))
    
    select len(t),t from #t
    
    UPDATE #t
    SET t = SUBSTRING(t, 1, LEN(t)-2 )
    WHERE t LIKE '%'+CHAR(13)+CHAR(10)
    
    select len(t),t from #t
    drop table #t

    If you save the output results to a text file the CRLF is still displayed. That is to say you can click below the last line of actual text.




    • Edited by marc111h Wednesday, July 31, 2013 2:39 PM
    Wednesday, July 31, 2013 2:21 PM
  • The UPDATE statement appears to be working correctly with the sample data you posted.

    Are you saying that the issue isn't with the data in the table but with the extra CRLF that is added when you do a Save Result As to a CSV file from SQL Server Management Studio?

    Wednesday, July 31, 2013 2:46 PM
  • I believe that is correct. I have copied and pasted to excel and saved as a text and csv file. Although the mid text CRLFs do not correctly format text when pasted into Excel the concern is that there is still a space and or characters at the end of the string.

    In the text file the mid CRLFs DO cause the text to rap, but again there is still an extra line after the string, at least on the last record.

    I actually wrote some functions in Excel and the CRLFs are ALL removed with the above code, including those mid string, although the positions are retained as space.


    • Edited by marc111h Wednesday, July 31, 2013 4:37 PM
    Wednesday, July 31, 2013 3:16 PM
  • I am not sure I understand your response. I must NOT remove ALL CR LFs, only the CRLF on end of string.

    If you save the results to a text file and open the text file you will find a blank line after the ending text. This is what needs to be removed.


    My query removes the CRLF in the end only. Have a look at the sample data provided in the query.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, August 01, 2013 5:11 PM
  • >If you save the output results to a text file the CRLF is still displayed.

    That is because the file saving software puts it in as row terminator.

    Use binary to look into a string:

    create table #t(t varchar(100))
    insert into #t values('abc'+char(13)+char(10)+'xyz'+char(13)+char(10))
    insert into #t values('defgh')
    insert into #t values('abc1234'+char(13)+char(10)+'xyz987'+char(13)+char(10)+'lmnop345'+char(13)+char(10))
    
    select len(t),t from #t
    
    UPDATE #t
    SET t = SUBSTRING(t, 1, LEN(t)-2 )
    WHERE t LIKE '%'+CHAR(13)+CHAR(10)
    
    select CONVERT(binary(30), t), len(t),t from #t
    drop table #t


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012



    Monday, August 12, 2013 10:21 PM
    Moderator