Post reply

REPLACE 2 lines CR+LF by 1 line CR+LF

  • January 26, 2021 at 4:37 pm

    #3837680

    In a table, I have a field (text format) which contains sometimes 2 or 3 consecutive empty lines of Carriage return + Line feed

    I would like replace these 2 or 3 lines by only 1 line CR+LF

     

    Could you help me to build the sql query?

     

    Many thanks

    • This topic was modified 3 years, 3 months ago by  ddperso.

  • January 26, 2021 at 5:07 pm

    #3837701

    To handle one pair of consecutive CRLF, you'd use one replace. For additional consecutive CRLF, you nest additional replace statements.

    DECLARE @crlf char(2) = CHAR(13) + CHAR(10)
    DECLARE @doubleCRLF char(4) = @crlf  + @crlf ;


    -- Two replaces handle 3 consecutive CRLF
    UPDATE tablename SET field = REPLACE(REPLACE(field,@doubleCRLF,@crlf),@doubleCRLF,@crlf)

  • January 26, 2021 at 6:10 pm

    #3837717

    Many thank's ratbak

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply