How to check if field is null or empty in MySQL

Question

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     quot  quot  something like  SELECT IFNULL field1   empty   OR field1     quot  quot   as field1 from tablename  Any idea how to accomplish this

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt 0  DR CR  0  0  AS DR  ROUND IF  DR CR  lt 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 gt      FROMDATE      AND t entdt lt      TODATE        VTYPE       ORDER BY CAST ENTDT AS DATE   AS ta

User · Answer

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

User · Answer

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

User · Answer

Try using nullif   SELECT ifnull nullif field1      empty   AS field1   FROM tablename

[mysql] How to check if field is null or empty in MySQL?

Examples related to mysql

Examples related to sql