How I can remove all NewLine from a variable in SQL Server?

Question

How can I can remove all NewLine from a variable in SQL Server?

I use SQL Server 2008 R2.

I need remove all NewLine in a variable in a T-Sql Command.

For example:

Declare @A NVarChar(500) 
Set @A = ' 12345
        25487
        154814 '
Print @A

And it printed like this:

 12345
        25487
        154814

But I want to get strings like this:

12345 25487 154814

I write this query, but it does not work:

Set @A = Replace(@A,CHAR(13),' ')

to see what ascii characters are http://technet.microsoft.com/en-us/library/ms177545.aspx — Justin, Aug 06 '13 at 06:49
Possible duplicate of [Replace a newline in TSQL](https://stackoverflow.com/questions/951518/replace-a-newline-in-tsql) — Jesse Webb, Dec 08 '17 at 20:19

score 29 · Accepted Answer · edited Sep 12 '13 at 14:47

29

You must use this query

Declare @A NVarChar(500);

Set @A = N' 12345
        25487
        154814 ';

Set @A = Replace(@A,CHAR(13)+CHAR(10),' ');

Print @A;

edited Sep 12 '13 at 14:47

Aaron Bertrand

272,866
37
466
490

answered Aug 06 '13 at 06:38

Ardalan Shahgholi

11,967
21
108
144

score 3 · Answer 2 · edited May 23 '17 at 11:54

If you want it to look exactly like in your sample output, use this hack:

DECLARE @A nvarchar(500) 
SET @A = ' 12345
        25487
        154814 '

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

PRINT @A

It will first replace your newline's then your consecutive spaces with one. Pay attention that it would be wise to url-encode the input string to avoid nasty surprises.

score 3 · Answer 3 · answered Mar 29 '19 at 14:40

3

Replace(@A,CHAR(13)+CHAR(10),' ') didn't remove all the spaces for me.

Instead I used

replace(replace(@A, char(13),N' '),char(10),N' ')

This works well!

answered Mar 29 '19 at 14:40

user11278100

31
1

score 0 · Answer 4 · edited May 20 '20 at 15:45

0

The hack with <> is nice, but may not work if there are <> in the text already (like in HTML).

It would be better to use char(13) and char(10) instead. First, replace char(13) with char(13)+char(10), char(10) with char(13)+char(10), and ' ' also with char(13)+char(10) to resolve not having both.

Then, replace char(10)+char(13) with '' and finally char(13)+char(10) with space.

edited May 20 '20 at 15:45

double-beep

5,031
17
33
41

answered May 20 '20 at 14:18

Martin Heisler

1

How I can remove all NewLine from a variable in SQL Server?

4 Answers4

Linked