A colleague asked me today if I there was a way of checking if a varchar field in our database contained a carriage return and line feed.

To take a look at this I used a simple table with just a varchar column on it - defined as
CREATE TABLE [dbo].[MyTest]
(
   [Test1] [varchar](20) NULL
) ON [PRIMARY]

and inserted some data with a carriage return in it

INSERT INTO MyTest(Test1)
VALUES
('This is
my data')

When looked at through the grid view of SQL Server Management Studio it appears as
This is my data and this is where the confusion was coming in - those control characters had been replaced with spaces - so how could we tell what they were.

When we looked at the data through through the text view of SQL Server Management Studio it appears as
This is
my data

And to double check I did a search using SQL as
SELECT *
FROM MyTest
WHERE Test1 LIKE '%' + CHAR(13) + '%'