MySQL LENGTH() > 0 and NOT NULL not getting expected results

Question

Viewing my result set, I see a blank field despite my conditions.

I tried selecting:

SELECT column
FROM table
WHERE LENGTH(column) > 0 AND column IS NOT NULL

i also tried:

WHERE LENGTH(column) <> 0 AND column IS NOT NULL

but, I'm still seeing a blank field.

In my SELECT, I tried checking the contents of the field:

SELECT column, LENGTH(column), HEX(column)

etc...

But, they both come up as 0 and seemingly empty, respectively.

What did I miss here?

deleting my answer as you alreay tried it..which language are you using to retrieve the data, can you post the code? — Oli, Mar 07 '13 at 22:39
I'm using MySQL `SELECT co.contact_email, LENGTH(co.contact_email), HEX(co.contact_email), co.contact_client, cl.client_name FROM contacts as co JOIN clients as cl ON co.contact_client = cl.client_oldid WHERE cl.client_status = 2 order by cl.client_name AND LENGTH(co.contact_email) > 0 AND co.contact_email is not null AND TRIM(co.contact_EMAIL) <> ''` — Mailman, Mar 07 '13 at 22:41
Also, the field attributes are: varchar(80) and cannot be null — Mailman, Mar 07 '13 at 22:48

score 1 · Accepted Answer · answered Mar 07 '13 at 22:46

  SELECT 
      ....  
  FROM contacts as co 
  JOIN clients as cl 
    ON co.contact_client = cl.client_oldid 
  -- this starts a where clause
  WHERE cl.client_status = 2 
  -- ORDER BY ends a WHERE clause, and goes only for ordering:
  order by 
    cl.client_name  
    AND LENGTH(co.contact_email) > 0  -- so, order by result of this 0 or 1
    AND co.contact_email is not null  -- then, order by result of this 0 or 1
    AND TRIM(co.contact_EMAIL) <> '   -- then, order by result of this 0 or 1

Well don't I feel sheepish. Thanks everyone. – Mailman Mar 07 '13 at 22:51 — Mailman, Mar 07 '13 at 22:51

MySQL LENGTH() > 0 and NOT NULL not getting expected results

1 Answers1