How to check if a field is null or empty mysql?

Question

I am trying to figure out how to check if a field is null or empty. I have it

SELECT IFNULL(field1, 'empty') as field1 from tablename 

I need to add additional field1 != "" Something like

 SELECT IFNULL(field1, 'empty') OR field1 != "" as field1 from tablename 

Any idea how to do this?

Answer 1

Or 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 empty lines, you can also use ifnull() or coalesce(field1, 'empty') . But this is not suitable for empty lines.

Answer 2

Alternatively, you can also use CASE for this:

 SELECT CASE WHEN field1 IS NULL OR field1 = '' THEN 'empty' ELSE field1 END AS field1 FROM tablename. 

Answer 3

You can create a function to facilitate its execution.

 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 

Answer 4

You can use the IFNULL function inside 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 

Answer 5

If you want to test PHP, you need to 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!"; } 

Answer 6

 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