mysql select where column not empty

Question

Hi,

Trying to figure out if this is possible select columns only where something exists?

Example, I have the following query

select phone, phone2
from jewishyellow.users
where phone like '813%'
and phone2

Basically, I'm trying to select only the rows where phone starts with 813 and phone2 has something in it?

Answer 1

Compare value of phone2 with empty string:

select phone, phone2 
from jewishyellow.users 
where phone like '813%' and phone2<>""

Note that NULL value is interpreted as false.

Answer 2

select phone, phone2 from jewishyellow.users 
where phone like '813%' and phone2 is not null

Answer 3

SELECT phone, phone2 
FROM jewishyellow.users 
WHERE phone like '813%' and (phone2 <> "");

May need some tweakage depending on what your default value is. If you allowed Null fill, then you can do "Not NULL" instead, which is obviously better.

Answer 4

To check if field is NULL use IS NULL, IS NOT NULL operators.

MySql reference http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

Answer 5

If there are spaces in the phone2 field from inadvertant data entry, you can ignore those records with the IFNULL and TRIM functions:

SELECT phone, phone2
FROM jewishyellow.users
WHERE phone LIKE '813%'
    AND TRIM(IFNULL(phone2,'')) <> '';

Answer 6

Use:

SELECT t.phone, 
       t.phone2 
  FROM jewishyellow.users t
 WHERE t.phone LIKE '813%' 
   AND t.phone2 IS NOT NULL

Answer 7

Check for NULL and empty string values:

select phone
, phone2 
from users 
where phone like '813%' 
and trim(coalesce(phone2, '')) <>''

N.B. I think COALESCE() is SQL standard(-ish), whereas ISNULL() is not.