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

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

Your comment:



 (will not be displayed)


 
 
 
Please add 8 and 2 and type the answer here:
 

Live Comment Preview:

 
«December»
SunMonTueWedThuFriSat
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910

About
Article Categories
Archives
Post Categories
Links
Syndication:

RSS ATOM