CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Negar Jafarpisheh
Negar Jafarpisheh

Reputation: 413

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

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),' ')

Upvotes: 14

Views: 71583

Answers (4)

Martin Heisler
Martin Heisler

Reputation: 1

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.

Upvotes: 0

user11278100
user11278100

Reputation: 31

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!

Upvotes: 3

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12575

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;

Upvotes: 29

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

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.

Upvotes: 3

Related Questions