Remove Newline Tab Character in Data using SQL Server

While working on an application, I came across a small column with some Newline and Tab characters in it. The requirement was to replace these characters. Here’s how to remove/replace the Newline characters in your data. I will demonstrate this example with a string, but you can easily replace it with a column name

DECLARE @str as nvarchar(70)
SET @str = 'APPT 5212
NORTH BLOCK, 7th Av
MUMBAI,MAH'

SELECT @str AS 'Orginal String'

DECLARE @CrLf CHAR(2);
SET @CrLf = CHAR(13) + CHAR(10); -- CarriageReturn + LineFeed

SELECT
REPLACE(REPLACE(SUBSTRING(@str,1,DATALENGTH(@str)),@CrLf,'-'), CHAR(9), '')
AS 'String with No Newlines or TABS'

Read more: SQL Server curry

Posted via email from .NET Info