How to detect trailing line breaks (CRLFs) in SQL Server database table columns

Sometimes, you can end up in a situation where an SQL query doesn't return a value you expect, and if you look at the data with SQL Server Management Studio (SSMS), you see the data to exactly what you are querying, but the results don't match. Today, this happened to me, and the result was I immediately thought: my data had trailing line breaks (likely from Excel), but SSMS cleans them for you.

If you suspect a column contains hidden line breaks that cause comparison or display issues, here are two simple ways to detect CRLF characters in SQL Server.

The examples below use a table called Programs and a column named InvoiceReference, but the approach works for any character column.

Method 1: check for trailing CR, LF, or CRLF

This method explicitly inspects the last one or two characters of the column value.

SELECT ProgramId, InvoiceReference
FROM Programs
WHERE RIGHT(InvoiceReference, 1) IN (CHAR(10), CHAR(13))
   OR RIGHT(InvoiceReference, 2) = CHAR(13) + CHAR(10);

The code CHAR(13) is a carriage return (CR) and CHAR(10) is a line feed (LF) as ASCII control characters. This catches values that end with either character, or with a proper CRLF sequence.

This approach is handy when you only care about trailing whitespace that breaks joins, comparisons, or exports.

Method 2: make CR and LF characters visible

If you want to see exactly where line breaks appear inside the value, you can replace them with markers.

SELECT InvoiceReference, REPLACE(REPLACE(InvoiceReference, CHAR(13), '[CR]'), CHAR(10), '[LF]') AS Visible
FROM Programs
WHERE InvoiceReference LIKE '%' + CHAR(10) + '%';

This version helps when debugging data imported from files, APIs, or copy-paste operations. The Visible column shows where CR and LF characters are embedded.

Tip: you can extend the WHERE clause to also search for CHAR(13) if needed.

Hope this helps!