Remove NewLine characters from the data in SQL Server

UPDATE: Xavid  at 8/2/2007  in a comment suggested a simpler code:

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

 


I found that some string in the database have NewLine characters where they do not required.

To remove them in T-SQL I wrote the following SQL script (TODO: write  re-usable SP, also special option to remove NewLine characters from the end of the string)

declare @NewLine char(2)
set @NewLine=char(13)+char(10)
update TableName
     set ColumnName =Replace(ColumnName , @NewLine,'')
WHERE ColumnName like '%' +@NewLine +'%'

Note that even if WHERE condition may  look redundant,  it is important for performance. Without the (ColumnName like '%' +@NewLine +'%')condition all records will be updated,even if actual column value would not be changed.

To identify rows with newLine at the end the following condition can be used.

where ( RIGHT(ColumnName ,2)=@NewLine

See ASCII Characters for values of different characters .

posted @ Tuesday, November 15, 2005 11:08 AM
Print

Comments on this entry:

# re: Remove NewLine characters from the data in SQL Server

Left by Cori at 5/17/2006 1:47 AM
Gravatar
Thank you! This is exactly what I needed!

# re: Remove NewLine characters from the data in SQL Server

Left by Oskar at 10/24/2006 10:42 PM
Gravatar
Unfortunately this doesn't work for me. At least in this function:

create function dbo.get_spid_sql
( @p_sql_handle binary(20) )
returns varchar(8000)
as
begin
declare @curr_stmt varchar(8000)
declare @nl char(2)

select @curr_stmt = [text] from ::fn_get_sql(@p_sql_handle)

set @nl = char(13) + char(10)

set @curr_stmt = replace(@curr_stmt, @nl, ' ')

return @curr_stmt
end

As you can see I want to take a sql handle and return its statement without returns, but that doesn't work.

# re: Remove NewLine characters from the data in SQL Server

Left by Michael Freidgeim at 10/25/2006 9:38 AM
Gravatar
Oscar,

Are you sure that your new line separators are (char(13)+char(10))?
In some encodings ony single char(13) or char(10) could be used. I even have seen another order (char(10)+char(13)).

# re: Remove NewLine characters from the data in SQL Server

Left by doel Bera at 7/24/2007 8:02 PM
Gravatar
Actually in a description column someone is pressing enter button ,so in the description column i have to remove enter and I have to concatenate total sentence.

# re: Remove NewLine characters from the data in SQL Server

Left by Xavid at 8/2/2007 7:42 PM
Gravatar
use this when replacing. It gets everything

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

# re: Remove NewLine characters from the data in SQL Server

Left by Sundoro at 10/1/2007 5:41 PM
Gravatar
Michael....thank you very much for your help.

# Remove NewLine characters from the data in SQL Server

Left by Rajesh.Dharmakkan at 10/23/2007 10:24 PM
Gravatar
Thank you. I am searching this replace query for a long time. Thank u sir,


# re: Remove NewLine characters from the data in SQL Server

Left by Charles at 10/26/2007 4:09 AM
Gravatar
Hello guys, i try to uset it, but in iexplores is ok, but in Firefox not. I don't know what i have to do to in firefox also function.

# re: Remove NewLine characters from the data in SQL Server

Left by Michael Freidgeim at 10/26/2007 3:06 PM
Gravatar
Charles,
The article is about SQL, it's not related to browser.
You should look for some Javascript function.

# re: Remove NewLine characters from the data in SQL Server

Left by Reddeppa Reddy at 11/14/2007 6:05 PM
Gravatar
Thank you very much!!!!!
i was searching for this from long time...

# re: Remove NewLine characters from the data in SQL Server

Left by Charush at 12/6/2007 11:38 AM
Gravatar
Thanks a lot, Xavid!!!!

# re: Remove NewLine characters from the data in SQL Server

Left by ruchira at 12/31/2007 5:29 PM
Gravatar
Thank u very much!

# re: Remove NewLine characters from the data in SQL Server

Left by ANONYMOUS at 1/9/2008 1:37 AM
Gravatar
Thanks a lok

# re: Remove NewLine characters from the data in SQL Server

Left by Hari at 3/26/2008 6:10 AM
Gravatar
It helped me. I imported data from Excel Spread sheet to SQL Table. one junk character is deplaying at the end of some columns. I figured that was one of new line char. I always tried to replace with Char(13) but I did never worked for me. When I replced with Char(10), It worked fine. Thanks a lot for your good article.

# re: Remove NewLine characters from the data in SQL Server

Left by Harman at 5/6/2008 3:01 PM
Gravatar
Thanks Xavid!

# re: Remove NewLine characters from the data in SQL Server

Left by Payal at 7/14/2008 2:46 PM
Gravatar
Thanks! It worked for me.

# re: Remove NewLine characters from the data in SQL Server

Left by Lata at 7/22/2008 1:26 PM
Gravatar
Thanks Xavid!
I had been searching how to do it for quite a while and this really helped !

# re: Remove NewLine characters from the data in SQL Server

Left by divz at 9/17/2008 7:01 AM
Gravatar
this works for my query:

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

thanks a lot!!!!! WEEEEEEEEE!!!

# re: Remove NewLine characters from the data in SQL Server

Left by Dylan at 9/29/2008 1:57 PM
Gravatar
I had been trying to clean up some legacy HTML page content data after an import from Access to SQL Server 2005 using the Microsoft SSMA tool. There were all of those little boxes displayed in the grid view when I viewed the data. I was also trying to combining several small Access databases into a single MSQL database and so it was useful to copy and paste some rows from one imported table to my new production tables. I was only able to copy and paste rows if the little boxes for carriage returns and line feeds were removed. In order to that I used the following code to execute directly on the database in SQL Server. The first statement replaces the new lines with HTML breaks so that the website still displays as intended. The second statement just removes the boxes so the rows can be copied and pasted from one MSQL table to another.

UPDATE tblContent
SET Author = REPLACE(Author,char(13),'<br>')
WHERE Author LIKE '%'+char(13)+'%'

UPDATE tblContent
SET Copy = REPLACE(Copy,char(10),'')
WHERE Copy LIKE '%'+char(10)+'%'

# re: Remove NewLine characters from the data in SQL Server

Left by Amol Lokhande at 10/6/2008 3:48 AM
Gravatar
Thanks a lot. This really helped me

# re: Remove NewLine characters from the data in SQL Server

Left by Sam at 12/11/2008 8:45 AM
Gravatar
Thank you Xavid.

It worked!

# re: Remove NewLine characters from the data in SQL Server

Left by Frank Lan at 12/18/2008 12:24 PM
Gravatar
Way to go guys, way to go!
...@_@...

# re: Remove NewLine characters from the data in SQL Server

Left by Sairam at 3/5/2009 5:45 PM
Gravatar
Hey its really very good ...it helped me a lot...Thanks once again for sharing knowledge....

# re: Remove NewLine characters from the data in SQL Server

Left by T Philip at 4/1/2009 10:11 PM
Gravatar
Thanks ... It was of great use. .. :)

# re: Remove NewLine characters from the data in SQL Server

Left by Trey at 5/9/2009 2:37 AM
Gravatar
I wanted to pass on my find. BTW, this helped a lot. Big props go out to Xavid!!!

Dylan had a good idea too...

here is THE MOST simple way to correct this issue for a single instance:


!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Update CABLEPR
set [field_name] = REPLACE(REPLACE(REPLACE([field_name], CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

# re: Remove NewLine characters from the data in SQL Server

Left by shanke at 5/27/2009 2:11 PM
Gravatar
very very useful stuff! Thanks guys!

# re: Remove NewLine characters from the data in SQL Server

Left by Jagdish at 7/6/2009 6:44 AM
Gravatar
Thank you.

# re: Remove NewLine characters from the data in SQL Server

Left by web development company at 8/14/2009 4:50 AM
Gravatar
Interesting,

oh I had to write load and load of codes just to do this simple tast

Keep up the good work

# re: Remove NewLine characters from the data in SQL Server

Left by louie at 8/19/2009 1:43 AM
Gravatar
thank you for this blog, it is realy helpful. Thanks much!

# re: Remove NewLine characters from the data in SQL Server

Left by Tejesh Kamble at 8/20/2009 6:58 AM
Gravatar
Thanks...........
This works for me....
I was searching for that.........
once again Thanks for this post.......

# re: Remove NewLine characters from the data in SQL Server

Left by Rohit at 8/26/2009 8:35 AM
Gravatar
Thanks, It worked for me

# re: Remove NewLine characters from the data in SQL Server

Left by Jignesh at 9/8/2009 2:24 AM
Gravatar
Thanks, it really solved my Problem. Thanks again

# re: Remove NewLine characters from the data in SQL Server

Left by Michael Haren at 10/9/2009 1:41 PM
Gravatar
Very helpful, thanks!

# re: Remove NewLine characters from the data in SQL Server

Left by Renga at 12/14/2009 8:47 PM
Gravatar
Very helpful, thanks!

# re: Remove NewLine characters from the data in SQL Server

Left by John Doe at 1/17/2010 12:41 AM
Gravatar
Exactly what i was looking for. It work like a charm. Perfect. Thanks a ton.

# re: Remove NewLine characters from the data in SQL Server

Left by Masrawy at 3/14/2010 10:19 PM
Gravatar
Thanks for your help, you saved my life.

# re: Remove NewLine characters from the data in SQL Server

Left by Hasan at 3/30/2010 6:08 AM
Gravatar
A lot of thnx, really i need this toooooooooooooo much thnx thnx and thnx

# NewLine characters from the data in SQL Server

Left by sandeep at 4/10/2010 12:04 AM
Gravatar
I am trying to insert new line character like
set @NewLine=char(13)+char(10) in sql server but it will not work in sql server 2008..i don't known why ..u have any new line option for sql server 2008.....................

# re: Remove NewLine characters from the data in SQL Server

Left by politz at 4/21/2010 12:50 PM
Gravatar
Thanks Xavid!!!

# re: Remove NewLine characters from the data in SQL Server

Left by sanjeev at 5/25/2010 7:24 AM
Gravatar
Thanks a lot It saved my time

# re: Remove NewLine characters from the data in SQL Server

Left by SUAT at 12/25/2010 5:17 AM
Gravatar
Thansks very good !!!

# How to add NewLine characters in SQL Server

Left by Siju at 1/13/2011 7:30 AM
Gravatar
I used the following query...
Select 'This is'+ char(13)| + 'for test' as Test
But Its work in query result as text.
But Its not work in query result as grid. But i need to display the result in grid what i can do for this..

# re: Remove NewLine characters from the data in SQL Server

Left by Manikandan at 1/22/2011 8:08 AM
Gravatar
Thanks Xavid!!!

# re: Remove NewLine characters from the data in SQL Server

Left by Anand at 2/4/2011 6:06 AM
Gravatar
Great Thanks....

# re: Remove NewLine characters from the data in SQL Server

Left by Mohamed Abbas at 3/14/2011 1:17 AM
Gravatar
Thanks i used it for adding new not removing it

# charre: Remove NewLine acters from the data in SQL Server

Left by Narender at 4/21/2011 6:19 AM
Gravatar
Excellent!!!
Resolved a big issue for me.

# re: Remove NewLine characters from the data in SQL Server

Left by Mohana at 4/28/2011 7:07 PM
Gravatar
Thank you very much for this info. It is very helpful.

# re: Remove NewLine characters from the data in SQL Server

Left by Kunal at 5/19/2011 7:29 AM
Gravatar
Thank You very much Solved my problem

# re: Remove NewLine characters from the data in SQL Server

Left by mani at 5/26/2011 6:52 AM
Gravatar
it worked fine.... thanks a lot...

# re: Remove NewLine characters from the data in SQL Server

Left by Musawar at 6/20/2011 8:36 AM
Gravatar
thnx dear

# re: Remove NewLine characters from the data in SQL Server

Left by Kamal at 7/27/2011 7:49 AM
Gravatar
Hi Guys,

Thanks for this post, It actually saved my days
Please keep posting !

Thanks
Kamal Patel.

# re: Remove NewLine characters from the data in SQL Server

Left by Karan at 8/29/2011 5:55 AM
Gravatar
For oracle 10 g its CHR instead of CHAR

# re: Remove NewLine characters from the data in SQL Server

Left by DIPU at 9/24/2011 6:16 AM
Gravatar
THANX XDAVID

# re: Remove NewLine characters from the data in SQL Server

Left by Jan at 10/21/2011 3:29 AM
Gravatar
Thank you Xavid!!!

I had problems with exporting the resultset to excel and this solved the problem.

# re: Remove NewLine characters from the data in SQL Server

Left by ELAYA at 11/21/2011 5:50 AM
Gravatar
New Line characters are hiding when we do Export to Excel

# re: Remove NewLine characters from the data in SQL Server

Left by evren at 11/22/2011 9:01 AM
Gravatar
thanks..
worked for me too

# re: Remove NewLine characters from the data in SQL Server

Left by Mohammad Safdel at 2/8/2012 7:16 AM
Gravatar
very thanx. This is exactly what I needed!

# re: Remove NewLine characters from the data in SQL Server

Left by Singh at 2/19/2012 1:27 AM
Gravatar

For Oracle databases, one can make use of REPLACE function as follows -
REPLACE(Con.COMMENTS, CHR(13) || CHR(10), ‘ ‘) as “Updated_Comments”

For a detailed example, please refer to following link:
http://crackingsiebel.wordpress.com/2010/08/21/oracle-sql-replace-newline-or-linefeed-character/

# re: Remove NewLine characters from the data in SQL Server

Left by Clons at 6/21/2012 4:29 AM
Gravatar
Thank You very much Solved my problem :)

# re: Remove NewLine characters from the data in SQL Server

Left by VK at 10/10/2012 1:50 AM
Gravatar
Thanks a lot, Finally its worked out

# re: Remove NewLine characters from the data in SQL Server

Left by Trung Vo at 2/27/2013 7:16 PM
Gravatar
Thank you very much, Xavid.

# re: Remove NewLine characters from the data in SQL Server

Left by Yukesh at 5/15/2013 11:41 PM
Gravatar
Thank you Xavid for such a wonderful blog!!! You are marvellous :) !!

# re: Remove NewLine characters from the data in SQL Server

Left by Ranjit at 7/19/2013 1:32 AM
Gravatar
Thankyou somuch.. Xavid, its really worked for me.

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

# re: Remove NewLine characters from the data in SQL Server

Left by htm11 at 7/31/2013 3:05 AM
Gravatar
This query replaces all instance of CR LF the where ( RIGHT(data_text ,2)=@NewLine) does not work, it still removes all instances of CR LF in the text string. I have multiple sentences in my text and only need to remove the CR LF from the end of the record.

# re: Remove NewLine characters from the data in SQL Server

Left by Jorge at 11/30/2013 2:46 AM
Gravatar
Hi, I´ve been working with REPLACE for changing a '.' for a ',' and it worked perfect. Although, when I specify char(13), equivalen to CR there is an error: Ora 0936: MIssing expression and points the Char(13) in the sentence. Could be a problem with the SQL version, in that case, what sentence should I use instead of Char()?
I appreciate a lot your help.

Regards,
Jorge

Your comment:



(not displayed)

 
 
 
 

Live Comment Preview:

 
«December»
SunMonTueWedThuFriSat
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910