How to check if field is null or empty in MySQL?
I am trying to figure out how to check if a field is NULL
or empty. I have this:
SELECT IFNULL(field1, 'empty') as field1 from tablename
I need to add an additional check field1 != ""
something like:
SELECT IFNULL(field1, 'empty') OR field1 != "" as field1 from tablename
Any idea how to accomplish this?
Either use
SELECT IF(field1 IS NULL or field1 = '', 'empty', field1) as field1
from tablename
or
SELECT case when field1 IS NULL or field1 = ''
then 'empty'
else field1
end as field1
from tablename
If you only want to check for null
and not for empty strings then you can also use ifnull()
or coalesce(field1, 'empty')
. But that is not suitable for empty strings.
Try using nullif
:
SELECT ifnull(nullif(field1,''),'empty') AS field1
FROM tablename;
Alternatively you can also use CASE
for the same:
SELECT CASE WHEN field1 IS NULL OR field1 = ''
THEN 'empty'
ELSE field1 END AS field1
FROM tablename.