Wednesday, March 18, 2009

Find /Remove New Line or Carriage Return Character in String or Text in SQL Server

1)

DECLARE @NewLine char(2)
SET @NewLine=char(13)+char(10)

SELECT *
FROM TABLENAME
WHERE CHARINDEX(@NewLine,ColumnName) > 0


2)

DECLARE @NewLine char(2)
SET @NewLine=char(13)

SELECT *
FROM TABLENAME
WHERE CHARINDEX(@NewLine,ColumnName) > 0



3)

DECLARE @NewLine char(2)
SET @NewLine=char(10)

SELECT *
FROM TABLENAME
WHERE CHARINDEX(@NewLine,ColumnName) > 0

3 comments:

robin said...

1st one is appropraite to remove a new line char from string. As with new line char, it bring both Line feed(LF) and carriage return(CR).

Anonymous said...

Thank you so much.. appreciate!..

Anonymous said...

Awesome Post!! Thanks much!!!