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

When is a blank not a null or ''

From
mike
Date:
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

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

From
Alban Hertroys
Date:
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

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

From
Troels Arvin
Date:
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


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

From
Michael Kleiser
Date:
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 
Are NULL in work_email possible ?
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;

By the was in Oracle there is no difference between empty
CHAR- or VARCHAR-column and NULL- CHAR or VARCHAR-columns.
But that is scpecial to Oracle.


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

From
Sean Davis
Date:
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


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

From
Alban Hertroys
Date:
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

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

From
mike
Date:
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)


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

From
mike
Date:
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

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

From
Chris Green
Date:
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."

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

From
Martijn van Oosterhout
Date:
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.

Attachment

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

From
Csaba Nagy
Date:
[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.



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

From
"Berend Tober"
Date:
>>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


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

From
Sean Davis
Date:
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


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

From
Michael Kleiser
Date:
Maybe other whitspace or non-printable-character.
Try:

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


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