Обсуждение: When is a blank not a null or ''

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

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:

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 != '';

--
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban@magproductions.nl
W: http://www.magproductions.nl

On Wed, 02 Feb 2005 09:59:30 +0000, mike wrote:

> 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

An idea: You have " "-values in your work_email column, i.e. work_email
values consisting of space(s).

--
Greetings from Troels Arvin, Copenhagen, Denmark

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org 

SELECT first_name,work_email FROM tb_contacts  WHERE tb_contacts.work_email <>''
AND  tb_contacts.work_email IS NOT NULL;

Is there a newline or carriage return in the "blank" emails?

Sean

On Feb 2, 2005, at 4:59 AM, 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org

mike wrote:
>>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

Then you probably have email addresses that exist of white space only.
You should probably put a constraint on that if undesirable.

Try using a regular expression like so:

SELECT first_name,work_email
FROM tb_contacts
WHERE work_email !~ '^[[:space:]]*$';


--
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban@magproductions.nl
W: http://www.magproductions.nl

On Wed, 2005-02-02 at 11:31 +0100, Troels Arvin wrote:
> On Wed, 02 Feb 2005 09:59:30 +0000, mike wrote:
>
> > 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
>
> An idea: You have " "-values in your work_email column, i.e. work_email
> values consisting of space(s).
>

nope

SELECT work_email FROM tb_contacts  WHERE tb_contacts.work_email ILIKE
'% %';
 work_email
------------
(0 rows)

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

On Wed, Feb 02, 2005 at 09:59:30AM +0000, 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
>
Maybe you have some entries in the work_email column set to one or
more spaces.

--
Chris Green (chris@areti.co.uk)

    "Never ascribe to malice that which can be explained by incompetence."

Try:

SELECT first_name,'['||work_email||']' FROM tb_contacts  WHERE
tb_contacts.work_email <>'';

Maybe you have spaces?

On Wed, Feb 02, 2005 at 09:59:30AM +0000, 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

[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.

>>anyone any ideas
> If yes you should you have to use.
>
> SELECT first_name,work_email FROM tb_contacts  WHERE
>   tb_contacts.work_email <>''
> AND
>   tb_contacts.work_email IS NOT NULL;
>

See what happens with

SELECT first_name, work_email, LENGTH(COALESCE(work_email, ''))
FROM tb_contacts
WHERE LENGTH(TRIM(COALESCE(work_email, ''))) = 0

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

SELECT first_name, '[' || work_email || ']', ASCII(work_email) FROM tb_contacts  WHERE
tb_contacts.work_email <>'';

On Wed, 2005-02-02 at 11:31 +0100, Troels Arvin wrote: 

On Wed, 02 Feb 2005 09:59:30 +0000, mike wrote:
   

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     

An idea: You have " "-values in your work_email column, i.e. work_email
values consisting of space(s).
   

nope

SELECT work_email FROM tb_contacts  WHERE tb_contacts.work_email ILIKE
'% %';work_email
------------
(0 rows)


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org