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