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