How to remove a Carriage Return Line Feed (CRLF) from a String of Varchar Column in SQL Server

Removing a Carriage Return, Line Feed (CRLF) from a Column in SQL Server

The term CRLF refers to Carriage Return (ASCII CHAR(13), \r) Line Feed (ASCII CHAR(10), \n). They're used to note the termination of a line. In Windows both a CR and LF are required to note the end of a line, whereas in Linux/UNIX a LF is only required. In the HTTP protocol, the CR-LF sequence is always used to terminate a line.

Now we will see we can remove CRLF from a String in a Column in SQL Server.

Suppose we have a Table as follows..

Create Table #Tbl_CRLF
(
Id Int Identity,
Str varchar(15)

)

--Inserting some data into the table

Insert into #Tbl_CRLF (Str)
Select 'abc '
Union All
Select 'ab
cd'
Union All
Select ' vwx'
Union All
Select 'vw x

y'

Select * From #Tbl_CRLF

Now from the above Table data, column 'Str' , we know the following things :

-- record 1 have the leading space

-- record 2 have the CRLF

-- record 3 have trailing space

-- record 4 also have the CRLF

But when we look at the records 2 and 4, it looks like they have Space ' ' in their values.

This assumption is wrong. we will check that now.

Select *, Len(Str) Str_Length, CharIndex(' ',Str) Space_Pos  

From #Tbl_CRLF

From the above query result we noticed following things :

-- In 'Str' value of record 2, no Space character was found, as it is not a Space but a CRLF.

-- In 'Str' value of record 4, the Space character was found at position 3 between 'vw' and 'x', but not between 'x' and 'y'. So that we can conclude that there is a CRLF between the 'x' and 'y'.

Now assume those all are spaces and try to use Replace function and see the result.

Select *, Replace(Str,' ','') Str_No_Space,

Len(Replace(Str,' ','')) Str_Length, 

CharIndex(' ',Replace(Str,' ','')) Space_Pos From #Tbl_CRLF

Notes:

From the above query result, we noticed following things :

-- Records 2 and 4 returns with 'Str' values looks like having Space but they are CRLF.

-- Replace () Function not able to replace the that Space as it is CRLF.

-- CharIndex () Function not able to Identify the CRLF Position as it is not a Space.

Finally we use the ASCII CHAR(13) to identify and remove Carriage Return( \r ), and use the ASCII CHAR(10) to identify and remove Line Feed ( \n ). 

These ASCII Characters we need to use in side the REPLACE() Function to remove the CRLF Character from a String.

Also you can add the LTRIM, RTRIM() Functions to remove the Leading and Trailing blanks.

Select *, LTRIM(RTRIM(
Replace(Replace(Replace(Str,CHAR(13),''),CHAR(10),''),' ',''))) Trim_Str,

Len(Replace(Replace(Replace(Str,CHAR(13),''),CHAR(10),''),' ','')) Trim_Str_Len,
CharIndex(CHAR(13),Str) CR_Pos ,
CharIndex(CHAR(10),Str) LF_Pos,
From #Tbl_CRLF

Result:

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------