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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
