When is a blank not a null or ''

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

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

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.

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

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

Sean

On Feb 2, 2005, at 4:59 AM, mike wrote:

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/

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

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

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: