Remove Line Breaks in Sql Server

by Virender
February 3, 2016
Many time we import data into Sql Server and copy paste data from one source to sql server. for example import data from excel, CSV etc. to Sql Server. Some time hidden character like line break, tab, carriage  return also paste into Sql Server field. Its also not viable and when you will use “Select” query it will not select required rows.
 Today I am showing how to remove line break and carriage return from column and get desire result.
Character code 
Tab – Char(9)
Line feed – Char(10)
Carriage return – Char(13) 
We can use following query to remove line feed and carriage return from column.
SELECT REPLACE(REPLACE( [Column] , CHAR(13), ‘ ‘), CHAR(10), ‘ ‘)
Example
1. Create a temp table
2. Insert record into #Student temp table.



3. Select all record from #Student table

4. New select Student having ClassCode=’1001′. But you will get only two records. Row having studentName ‘Martin’ will not select. 
 
Reason is that third record also included “Carriage return” into end.
5. Now run following query
 
Select * from #Student where REPLACE(REPLACE(ClassCode, CHAR(13), ‘ ‘), CHAR(10), ‘ ‘) =’1001’ 
 
It will show you all record having ClassCode=’1001′

Complete query to “Remove Line Breaks in Sql Server”

Create table #Student
(
ClassCode nvarchar(20),
StudentName nvarchar(20)
)

Insert into #Student values(‘1001′,’Paul’)
Insert into #Student values(‘1001’+CHAR(13),’Martin’)

— adding carriage return into ClassCode column

Insert into #Student values(‘1001′,’John’)

Select * from #Student

Select * from #Student where ClassCode=’1001′

— Row having studentName ‘Martin’ will not select

Select * from #Student where REPLACE(REPLACE(ClassCode, CHAR(13), ‘ ‘), CHAR(10), ‘ ‘) =’1001’

Keywords

Remove Line Breaks in Sql Server

Remove special charter in Sql Server

Remove Line break in Sql Server

Share:

Tags:

0 0 votes
Article Rating
Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
awais
1 year ago

A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. I was exactly searching for. Thanks for such post and please keep it up. Great work https://serverbrowse.com/

Related posts