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.
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?
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
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 '%*%'