Geeks With Blogs

Michael Freidgeim's Blog MS .Net Development

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 on Tuesday, November 15, 2005 11:08 AM SQL Server | Back to top


Comments on this post: Remove NewLine characters from the data in SQL Server

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thank you! This is exactly what I needed!
Left by Cori on May 17, 2006 1:47 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting 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.

Left by Oskar on Oct 24, 2006 10:42 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting 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)).
Left by Michael Freidgeim on Oct 25, 2006 9:38 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting 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.
Left by doel Bera on Jul 24, 2007 8:02 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
use this when replacing. It gets everything

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')
Left by Xavid on Aug 02, 2007 7:42 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Michael....thank you very much for your help.
Left by Sundoro on Oct 01, 2007 5:41 PM

# Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thank you. I am searching this replace query for a long time. Thank u sir,


Left by Rajesh.Dharmakkan on Oct 23, 2007 10:24 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting 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.
Left by Charles on Oct 26, 2007 4:09 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Charles,
The article is about SQL, it's not related to browser.
You should look for some Javascript function.
Left by Michael Freidgeim on Oct 26, 2007 3:06 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thank you very much!!!!!
i was searching for this from long time...
Left by Reddeppa Reddy on Nov 14, 2007 6:05 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks a lot, Xavid!!!!
Left by Charush on Dec 06, 2007 11:38 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thank u very much!
Left by ruchira on Dec 31, 2007 5:29 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks a lok
Left by ANONYMOUS on Jan 09, 2008 1:37 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting 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.
Left by Hari on Mar 26, 2008 6:10 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks Xavid!
Left by Harman on May 06, 2008 3:01 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks! It worked for me.
Left by Payal on Jul 14, 2008 2:46 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks Xavid!
I had been searching how to do it for quite a while and this really helped !
Left by Lata on Jul 22, 2008 1:26 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
this works for my query:

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

thanks a lot!!!!! WEEEEEEEEE!!!
Left by divz on Sep 17, 2008 7:01 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting 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)+'%'
Left by Dylan on Sep 29, 2008 1:57 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks a lot. This really helped me
Left by Amol Lokhande on Oct 06, 2008 3:48 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thank you Xavid.

It worked!
Left by Sam on Dec 11, 2008 8:45 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Way to go guys, way to go!
...@_@...
Left by Frank Lan on Dec 18, 2008 12:24 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Hey its really very good ...it helped me a lot...Thanks once again for sharing knowledge....
Left by Sairam on Mar 05, 2009 5:45 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks ... It was of great use. .. :)
Left by T Philip on Apr 01, 2009 10:11 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting 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), '')

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Left by Trey on May 09, 2009 2:37 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
very very useful stuff! Thanks guys!
Left by shanke on May 27, 2009 2:11 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thank you.
Left by Jagdish on Jul 06, 2009 6:44 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Interesting,

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

Keep up the good work
Left by web development company on Aug 14, 2009 4:50 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
thank you for this blog, it is realy helpful. Thanks much!
Left by louie on Aug 19, 2009 1:43 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks...........
This works for me....
I was searching for that.........
once again Thanks for this post.......
Left by Tejesh Kamble on Aug 20, 2009 6:58 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks, It worked for me
Left by Rohit on Aug 26, 2009 8:35 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks, it really solved my Problem. Thanks again
Left by Jignesh on Sep 08, 2009 2:24 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Very helpful, thanks!
Left by Michael Haren on Oct 09, 2009 1:41 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Very helpful, thanks!
Left by Renga on Dec 14, 2009 8:47 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Exactly what i was looking for. It work like a charm. Perfect. Thanks a ton.
Left by John Doe on Jan 17, 2010 12:41 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks for your help, you saved my life.
Left by Masrawy on Mar 14, 2010 10:19 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
A lot of thnx, really i need this toooooooooooooo much thnx thnx and thnx
Left by Hasan on Mar 30, 2010 6:08 AM

# NewLine characters from the data in SQL Server
Requesting 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.....................
Left by sandeep on Apr 10, 2010 12:04 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks Xavid!!!
Left by politz on Apr 21, 2010 12:50 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks a lot It saved my time
Left by sanjeev on May 25, 2010 7:24 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thansks very good !!!
Left by SUAT on Dec 25, 2010 5:17 AM

# How to add NewLine characters in SQL Server
Requesting 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..

Left by Siju on Jan 13, 2011 7:30 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks Xavid!!!
Left by Manikandan on Jan 22, 2011 8:08 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Great Thanks....
Left by Anand on Feb 04, 2011 6:06 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks i used it for adding new not removing it
Left by Mohamed Abbas on Mar 14, 2011 1:17 AM

# charre: Remove NewLine acters from the data in SQL Server
Requesting Gravatar...
Excellent!!!
Resolved a big issue for me.
Left by Narender on Apr 21, 2011 6:19 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thank you very much for this info. It is very helpful.
Left by Mohana on Apr 28, 2011 7:07 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thank You very much Solved my problem
Left by Kunal on May 19, 2011 7:29 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
it worked fine.... thanks a lot...
Left by mani on May 26, 2011 6:52 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
thnx dear
Left by Musawar on Jun 20, 2011 8:36 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Hi Guys,

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

Thanks
Kamal Patel.
Left by Kamal on Jul 27, 2011 7:49 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
For oracle 10 g its CHR instead of CHAR
Left by Karan on Aug 29, 2011 5:55 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
THANX XDAVID
Left by DIPU on Sep 24, 2011 6:16 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thank you Xavid!!!

I had problems with exporting the resultset to excel and this solved the problem.
Left by Jan on Oct 21, 2011 3:29 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
New Line characters are hiding when we do Export to Excel
Left by ELAYA on Nov 21, 2011 5:50 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
thanks..
worked for me too
Left by evren on Nov 22, 2011 9:01 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
very thanx. This is exactly what I needed!
Left by Mohammad Safdel on Feb 08, 2012 7:16 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting 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/
Left by Singh on Feb 19, 2012 1:27 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thank You very much Solved my problem :)
Left by Clons on Jun 21, 2012 4:29 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks a lot, Finally its worked out
Left by VK on Oct 10, 2012 1:50 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thank you very much, Xavid.
Left by Trung Vo on Feb 27, 2013 7:16 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thank you Xavid for such a wonderful blog!!! You are marvellous :) !!
Left by Yukesh on May 15, 2013 11:41 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thankyou somuch.. Xavid, its really worked for me.

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')
Left by Ranjit on Jul 19, 2013 1:32 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting 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.
Left by htm11 on Jul 31, 2013 3:05 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting 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
Left by Jorge on Nov 30, 2013 2:46 AM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Thanks a lot ...It is very much helpful
Left by vidhya on Apr 29, 2015 9:17 PM

# re: Remove NewLine characters from the data in SQL Server
Requesting Gravatar...
Wonderful it's worked for me!!
Left by Sathish on May 18, 2015 10:14 PM

Your comment:
 (will show your gravatar)


Copyright © Michael Freidgeim | Powered by: GeeksWithBlogs.net | Join free