Saturday, January 12, 2019

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

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.