Finding Empty ntext and varchar Fields in SQL

Just the other day, I ran into another one of those little ‘slips your mind until you need it’ items.

In SQL, when checking for empty (no, NOT null, empty) fields with data types of ntext or varchar, querying for >’’ is not valid.

So, how can I find the records with empty fields? The test here would be to check for ANY string.

Well, LIKE ‘_%’ would be the way to do it.

For example, looking for records with an empty 'description' field (data type ntext) in a table with 200 entries:

SELECT * FROM widgets WHERE description IS NULL OR description NOT LIKE ‘_%’ 

would return the 17 records with empty or null description fields.

Conversely,

SELECT * FROM widgets WHERE description LIKE ‘_%’

would return the 183 records where there is something in the description field.