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

Did you try something like:

select first_name, work_email
FROM tb_contacts
WHERE tb_contacts.work_email !~ '^\\s$';

If this works, then you may want to do something like:

update tb_contacts set work_email=NULL where work_email ~ '^\\s$';

to "clean" the data and then use a trigger to do the same process on
future inserts.

Sean

On Feb 2, 2005, at 6:24 AM, mike wrote:

> On Wed, 2005-02-02 at 11:26 +0100, Alban Hertroys wrote:
>> mike wrote:
>>> I have the following query (I have removed all nulls from the field
>>> as
>>> test)
>>>
>>> SELECT first_name,work_email FROM tb_contacts WHERE
>>> tb_contacts.work_email <>'';
>>>
>>> However I get loads of blank email addresses coming up
>>>
>>> anyone any ideas
>>
>> A blank is never a NULL:
>
> I know, I meant visually a blank
>
>
>>
>> SELECT '' IS NULL;
>> ?column?
>> ----------
>> f
>> (1 row)
>>
>>
>> Try this:
>>
>> SELECT first_name,work_email FROM tb_contacts WHERE
>> tb_contacts.work_email IS NOT NULL;
>>
>> 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 != '';
>>
>
> no difference
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq