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.
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%'
Surprisingly(as nobody else mentioned it before) found that the condition below does the job:
WHERE ORD(field_to_check) > 0
when we need to exclude both null and empty values. Is anybody aware of downsides of the approach?
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,'')) <> '';
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.
Use:
SELECT t.phone,
t.phone2
FROM jewishyellow.users t
WHERE t.phone LIKE '813%'
AND t.phone2 IS NOT NULL
In my case I had a varchar column, both the methods of IS NOT NULL & != '' didn't work, but the following worked for me. Just putting this out here.
SELECT * FROM `db_name` WHERE `column_name` LIKE '%*%'
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.
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
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.
SELECT phone, phone2
FROM users
WHERE phone LIKE '813%'
AND CHAR_LENGTH(phone2) > 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) > 0 Results (same)
users
813-123-4567 | 1
813-123-4567 | 0
users2
813-123-4567 | 1
813-123-4567 | 0
Alternatives
phone2 <> '' Results (different)
users
813-123-4567 | 1
users2
813-123-4567 | 1
813-123-4567 | 0
phone2 > '' Results (different)
users
813-123-4567 | 1
users2
813-123-4567 | 1
813-123-4567 | 0
COALESCE(phone2, '') <> '' Results (same)
Note: the results differ from phone2 IS NOT NULL AND phone2 <> '' 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 <> '' Results (different)
users
813-123-4567 | 1
users2
813-123-4567 | 1
813-123-4567 | 0
select phone, phone2 from jewishyellow.users
where phone like '813%' and phone2 is not null
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 > ''
Notice the > '' 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 > '','true','false');-- false
SELECT if('' > '','true','false');-- false
SELECT if(' ' > '','true','false');-- false
SELECT if('\n' > '','true','false');-- false
SELECT if('\t' > '','true','false');-- false
SELECT if('Yeet' > '','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.
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.
Source: Stackoverflow.com