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 use
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.
SELECT IF(field1 IS NULL or TRIM(field1) = '', 'empty', field1) as field1 FROM tablename.
Using nullif does the trick:
SELECT ifnull(nullif(field1,''),'empty or null') AS field1
FROM tablename;
How it works: nullif is returning NULL if field is an empty string, otherwise returns the field itself. This has both the cases covered (the case when field is NULL and the case when it's an empty string).
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.
You can use the IFNULL function inside the IF. This will be a little shorter, and there will be fewer repetitions of the field name.
SELECT IF(IFNULL(field1, '') = '', 'empty', field1) AS field1
FROM tablename
You can create a function to make this easy.
create function IFEMPTY(s text, defaultValue text)
returns text deterministic
return if(s is null or s = '', defaultValue, s);
Using:
SELECT IFEMPTY(field1, 'empty') as field1
from tablename
By trimming and comparing, we ensure we also take care of empty or tab or space character content in the field.
SELECT
CASE
WHEN LTRIM(RTRIM(col_1))='' or col_1 IS NULL THEN 'Not available'
ELSE col_1
END AS col_alias
FROM
my_table
IF (with Mysql 8.x at least) works on truthy/falsy values:
IF(NULL, 'truthy', 'falsy')
IF('', 'truthy', 'falsy')
IF(0, 'truthy', 'falsy') -- beware
IF('0', 'truthy', 'falsy') -- beware
Every one of the above will return 'falsy'. So if you have a field that can be null or empty string (ie left join, etc):
IF(myField, 'not empty', 'empty')
This will work just fine exactly the way you'd think it would (given the caveats of 0 and '0'). Kind of infuriating considering comparing NULL values to NON-NULL values gives inconsistent results.
select IF('x', 'not empty', 'empty') gives 'empty'SELECT * FROM (
SELECT 2 AS RTYPE,V.ID AS VTYPE, DATE_FORMAT(ENTDT, ''%d-%m-%Y'') AS ENTDT,V.NAME AS VOUCHERTYPE,VOUCHERNO,ROUND(IF((DR_CR)>0,(DR_CR),0),0) AS DR ,ROUND(IF((DR_CR)<0,(DR_CR)*-1,0),2) AS CR ,ROUND((dr_cr),2) AS BALAMT, IF(d.narr IS NULL OR d.narr='''',t.narration,d.narr) AS NARRATION
FROM trans_m AS t JOIN trans_dtl AS d ON(t.ID=d.TRANSID)
JOIN acc_head L ON(D.ACC_ID=L.ID)
JOIN VOUCHERTYPE_M AS V ON(T.VOUCHERTYPE=V.ID)
WHERE T.CMPID=',COMPANYID,' AND d.ACC_ID=',LEDGERID ,' AND t.entdt>=''',FROMDATE ,''' AND t.entdt<=''',TODATE ,''' ',VTYPE,'
ORDER BY CAST(ENTDT AS DATE)) AS ta
SELECT IF(TRIM(COALESCE(field1, '')) = '', '[ empty ]', field1) FROM tablename
field1 is null or any length of whitespace
See TRIM COALESCE and IS NULL for more info.
Also Working with null values from the MySQL docs
If you would like to check in PHP , then you should do something like :
$query_s =mysql_query("SELECT YOURROWNAME from `YOURTABLENAME` where name = $name");
$ertom=mysql_fetch_array($query_s);
if ('' !== $ertom['YOURROWNAME']) {
//do your action
echo "It was filled";
} else {
echo "it was empty!";
}
$isnull = (empty($ertom['YOUROWNAME']) ? empty : not empty); That's a little shorter..