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

at

3 comments:

  1. UnknownAugust 18, 2010 at 3:55 PM

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

    ReplyDelete
  2. AnonymousJune 10, 2011 at 12:23 PM

    Thank you so much.. appreciate!..

    ReplyDelete
  3. AnonymousNovember 27, 2012 at 7:19 AM

    Awesome Post!! Thanks much!!!

    ReplyDelete