MySQL select where column is not empty

Question

In MySQL  can I select columns only where something exists    For example  I have the following query   select phone  phone2 from jewishyellow users where phone like  813   and phone2   I m trying to select only the rows where phone starts with 813 and phone2 has something in it

User · Accepted Answer

Compare value of phone2 with empty string   select phone  phone2  from jewishyellow users  where phone like  813   and phone2 lt  gt      Note that NULL value is interpreted as false

User · Answer

We can use CASE for setting blank value to some char or String  I am using NA as Default string   SELECT phone     CASE WHEN phone2      THEN  NA  END AS phone2 ELSE ISNULL phone2 0   FROM jewishyellow users  WHERE phone LIKE  813

User · Answer

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

User · Answer

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       lt  gt

User · Answer

select phone  phone2 from jewishyellow users  where phone like  813   and phone2 is not null

User · Answer

An answer that I ve been using that has been working for me quite well that I didn t already see here  this question is very old  so it may have not worked then  is actually   SELECT t phone          t phone2    FROM jewishyellow users t  WHERE t phone LIKE  813       AND t phone2  gt       Notice the  gt     part  which will check if the value is not null  and if the value isn t just whitespace or blank    Basically  if the field has something in it other than whitespace or NULL  it is true  It s also super short  so it s easy to write  and another plus over the COALESCE   and IFNULL   functions is that this is index friendly  since you re not comparing the output of a function on a field to anything   Test cases   SELECT if NULL  gt      true   false   -- false SELECT if     gt      true   false   -- false SELECT if      gt      true   false   -- false SELECT if   n   gt      true   false   -- false SELECT if   t   gt      true   false   -- false SELECT if  Yeet   gt      true   false   -- true     UPDATE There is a caveat to this that I didn t expect  but numerical values that are zero or below are not greater than a blank string  so if you re dealing with numbers that can be zero or negative then DO NOT DO THIS  it bit me very recently and was very difficult to debug     If you re using strings  char  varchar  text  etc    then this will be perfectly be fine  just be careful with numerics

User · Answer

SELECT phone  phone2  FROM jewishyellow users  WHERE phone like  813   and  phone2  lt  gt         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

User · Answer

Another alternative is to look specifically at the CHAR LENGTH of the column values   not to be confused with LENGTH   Using a criteria where the character length is greater than 0  will avoid false positives when the column values can be falsey  such as in the event of an integer column with a value of 0 or NULL  Behaving more consistently across varying data-types   Which results in any value that is at least 1 character long  or otherwise not empty   Example https   www db-fiddle com f iQvEhY1SH6wfruAvnmWdj5 1  SELECT phone  phone2 FROM users WHERE phone LIKE  813   AND CHAR LENGTH phone2   gt  0   Table Data  users phone  varchar 12    phone2  int 10   813-123-4567        NULL  813-123-4567        1  813-123-4567        0  users2 phone  varchar 12    phone2  varchar 12   813-123-4567        NULL  813-123-4567         1   813-123-4567         0   813-123-4567             CHAR LENGTH phone2   gt  0 Results  same   users 813-123-4567         1 813-123-4567         0  users2 813-123-4567         1 813-123-4567         0     Alternatives  phone2  lt  gt     Results  different   users 813-123-4567         1  users2 813-123-4567         1 813-123-4567         0   phone2  gt     Results   different   users 813-123-4567         1  users2 813-123-4567         1 813-123-4567         0   COALESCE phone2       lt  gt     Results  same  Note  the results differ from phone2 IS NOT NULL AND phone2  lt  gt     which is not expected  users 813-123-4567         1 813-123-4567         0  users2 813-123-4567         1 813-123-4567         0   phone2 IS NOT NULL AND phone2  lt  gt     Results  different   users 813-123-4567         1  users2 813-123-4567         1 813-123-4567         0

User · Answer

Use   SELECT t phone          t phone2    FROM jewishyellow users t  WHERE t phone LIKE  813       AND t phone2 IS NOT NULL

User · Answer

you can use like operator wildcard to achieve this   SELECT t phone          t phone2  FROM jewishyellow users t WHERE t phone LIKE  813      AND t phone2 like   0-9      in this way  you could get all phone2 that have a number prefix

User · Answer

Check for NULL and empty string values   select phone   phone2  from users  where phone like  813    and trim coalesce phone2        lt  gt      N B  I think COALESCE   is SQL standard -ish   whereas ISNULL   is not

User · Answer

Surprisingly as nobody else mentioned it before  found that the condition below does the job   WHERE ORD field to check   gt  0    when we need to exclude both null and empty values  Is anybody aware of downsides of the approach

User · Answer

In my case I had a varchar column  both the methods of IS NOT NULL  amp        didn t work  but the following worked for me  Just putting this out here   SELECT   FROM  db name  WHERE  column name  LIKE

[sql] MySQL select where column is not empty

Examples related to sql

Examples related to mysql