Re: When is a blank not a null or ''

[snip]
> Or if there are also blanks among those e-mail addresses:
>
> SELECT first_name,work_email FROM tb_contacts WHERE
> tb_contacts.work_email IS NOT NULL AND tb_contacts.work_email != '';

The "tb_contacts.work_email IS NOT NULL" clause is superfluous, the
other condition will evaluate to false for null email anyway: a null
compared with any operator to any value is always null, which fails the
comparison. Generally any operator involving a null always results in
null, except a few special operators like "IS NULL" and some others.

In fewer words, the original statement will filter out both null and
empty string emails, but not emails with one or more space characters in
them. For example " " will be selected, but for a human it still looks
blank. I wonder what data type you have, cause e.g. if you have char(n),
that will be padded automatically with space characters
(see http://www.postgresql.org/docs/7.4/static/datatype-character.html).

I you do have space characters in the email field, you could use:

trim(both from tb_contacts.work_email) != ''
or
char_length(trim(both from tb_contacts.work_email)) != 0
See also:
http://www.postgresql.org/docs/7.4/static/functions-string.html

This should filter out all null, empty string, and only space emails.

HTH,
Csaba.