Remove Line Breaks in Sql Server

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