<< Update appSettings in App.Config for executable. | Home | Visual Studio 2005 Web Site Project- "Edit and Continue" is too restrictive. >>

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 Jason at 12/3/2008 10:06 AM
Gravatar
Thanks I'm using Sybase but this worked for me too. The char(9) was the piece that I was missing.

# 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!

Your comment:



 (will not be displayed)


 
 
 
 
 

Live Comment Preview:

 
«November»
SunMonTueWedThuFriSat
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345

About
Article Categories
Archives
Post Categories
Links
Syndication:

RSS ATOM