2

I have a column 'CN_Text' of nvarchar type which will store notes with multiple line breaks.

Example - 

Aaaaaaaa....



Bbbbb



Cccccccc

I want to replace multiple consecutive line breaks with single line break. Like 

Aaaaaaaa....

Bbbbb

Ccccccccc

How I can replace this multiple line breaks with single line break?

TTeeple
  • 2,913
  • 1
  • 13
  • 22
Asit Singh
  • 53
  • 1
  • 6

2 Answers2

5

First replace all the new line to a different set of characters then replace those characters with new line as below.

select string = replace(replace(replace('Aaaaaaaa....


Bbbbb


Cccccccc','
','<>'),'><',''),'<>','
')
Ranjit Singh
  • 3,715
  • 1
  • 21
  • 35
  • 2
    Cool trick. If all of his strings are `CRLF`, you could further simplify by simply replacing `LFCR` with an empty string. – jmc Aug 05 '15 at 20:31
  • This is going to be clean up script, which is going to alter all rows of large database. End of the script, expectation is there should not be a single string which has two consecutive line breaks. If in string anywhere there is more than one line breaks that need to be reduced to only single line break. Note : string has more than 10000 lines(including multiple consecutive empty lines) – Asit Singh Aug 05 '15 at 20:39
  • it was just an example, you can replace your new line with $##$ also, then replace #$$# with empty then finally replace $##$ with new line. – Ranjit Singh Aug 05 '15 at 20:42
  • Not working perfectly. It is removing all line breaks. I need one line break.if multiple consecutive lines are there. Input Text : 'Asit Singh KKKKKKKK ' OutPut Text : 'Asit Singh KKKKKKKK ' – Asit Singh Aug 06 '15 at 06:01
2

I think you are looking this:

DECLARE @A nvarchar(500) 
SET @A = 'Aaaaaaaa....


         Bbbbb


         Cccccccc';

SET @A = 
  replace(replace(replace(
    replace(
      replace(@A,
      CHAR(13)+CHAR(10),' '),
      ' ','<>'),'><',''),'<>',' '),
      ' ',CHAR(13)+CHAR(10));


PRINT @A

First remove all the break lines in your text, later change the consecutive spaces with just one, and finally change the space by the break line you need.

ocuenca
  • 38,548
  • 11
  • 89
  • 102
  • In this case, if in single line we have text with multi spaces it will replace with new line. e.g. ' AAAAA... BBBBB ccccccc' – Asit Singh Aug 06 '15 at 06:22
  • I want to set focus back to the place where it was before removing extra lines. How i can restore Cursor. – Asit Singh May 16 '16 at 09:22