Don’t get caught by ANSI PADDING, VARCHAR, and trailing whitespace!

One thing to be aware of when you have VARCHAR columns and are using ANSI PADDING: trailing whitespace is trimmed and not counted in your equality ( = / <> / LIKE) statements, so you might not get the results you are looking to be returned from your query.

In the same vein, the len() function will return the same value for two strings if one has trailing whitespace.  If you need to compare those two fields including trailing whitespace values, use datalength() instead.

Here’s some example T-SQL:

DECLARE @TABLE TABLE (FIELD VARCHAR(50))
 
INSERT @TABLE VALUES ('some text')
INSERT @TABLE VALUES ('some text       ')
INSERT @TABLE VALUES ('    some text')
 
-- returns 2 :
-- trailing spaces trimmed,
-- leading spaces count
SELECT '"' + FIELD + '"'
FROM @TABLE
WHERE FIELD = 'some text'
 
-- also returns 2
SELECT '"' + FIELD + '"'
FROM @TABLE
WHERE FIELD LIKE 'some text'
 
-- shows the difference between len()
-- and datalength()
SELECT FIELD, LEN(FIELD) AS 'Length', DATALENGTH(FIELD) AS 'DataLength'
FROM @TABLE

Hope this helps!

This entry was posted in MS SQL Server, T-SQL. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *