Testing for whitespace in SQL Server

Question

I've got some blank values in my table, and I can't seem to catch them in an IF statement.

I've tried

IF @value = '' and if @value = NULL and neither one catches the blank values. Is there any way to test whether or not a varchar is entirely whitespace?

AHA! Turns out I was testing for null wrong. Thanks.

Answer 1

if length(@value) = 0 or @value is null

Answer 2

(LTRIM(RTRIM(@Value))='' should do the trick.

Answer 3

To compare with NULL, use the IS NULL keyword.

--Generic example:
SELECT *
FROM MY_TABLE
WHERE SOME_FIELD IS NULL;

--Instead of    

SELECT *
FROM MY_TABLE
WHERE SOME_FIELD = NULL;

Answer 4

You may have fields with multiple spaces (' ') so you'll get better results if you trim that:

where ltrim(yourcolumnname) = ''

Answer 5

ltrim(rtrim(isNull(@value,''))) = ''

Answer 6

where length(rtrim(ltrim(yourcolumnname))) = 0 OR yourcolumnname is null