TSQL - To visually identify if there is a Carriage Return / New Line character within the VARCHAR column

Scenario:

We have a scenario, where in we need to know if there is any carriage return('\r') or a new line('\n') character is embedded in a VARCHAR/TEXT column. 

Solution: 

This cannot be simply achieved by using SELECT statement. PRINT function enables us to know the presence of such characters. Let us now see how.

Step 1:

 Let me a create simple table for your easy understanding using the following script. 

CREATE TABLE [dbo].[TestDB_Test1](
[COL1] [int] IDENTITY(1,1) NOT NULL,
[COL2] [varchar](150) NULL
) ON [PRIMARY]

GO

Step 2: 

Lets insert two rows for COL2, one  without carriage return/new line  and the other one with the carriage return/new line. 

-- Sample data without carriage return/ new line character

INSERT INTO [dbo].[TestDB_Test1]

           ([COL2])

     VALUES

           ('I am Line1. I am Line2')

GO

-- Sample data with carriage return/new line character

INSERT INTO [dbo].[TestDB_Test1]

            ([COL2])

VALUES

('I am Line1.' + CHAR(10)+ 'I am Line2')

Step 3: 

Run a SELECT query, to see how the data appears. 

SELECT * from dbo.TESTDB_Test1 

The two rows appears to be same after a SELECT query. 

Step 4: 

Create a variable to hold the value of COL2 using a select query. 

DECLARE @var varchar(150) 

SELECT @var = col2 FROM TestDB_Test1 where col1 = 1

PRINT @var

DECLARE @var varchar(150) 

SELECT @var = col2 FROM TestDB_Test1 where col1 = 2

PRINT @var

This clearly shows that the text in row 2 had a new line character in between.  In order to remove such redundant characters, one could always use REPLACE function.