sqlsql-serversql-server-2008selectstring-length

Selecting all with a length of less than 8 characters, and not null, and not empty

How would I select all of the values which are less than 8 characters in length, and not a null value and not an empty value?

This is what I have but it is still pulling in null values

select distinct
alphaidx, 
case when len(termdatex)<8 then termdatex end as termdatex
from table
where termdatex is not null and termdatex!=''
Solution

  • Your problem is in your case expression. Implicitly, anything not handled in one of the when clauses is returned as null. Here, since you don't filter them out in the where clause, any string with 8 or more characters will be returned as null from the case expression. To fix this, you could move all your logic to the where clause:

    SELECT DISTINCT alphaidx, termdatex
FROM   some_table
WHERE  termdatex IS NOT NULL AND termdatex != '' AND LEN(termdatex) < 8