CODEWITHSUNDEEP
mysqlsql
veereev
veereev

Reputation: 2688

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?

Upvotes: 125

Views: 341394

Answers (10)

Matthew Lenz
Matthew Lenz

Reputation: 351

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.

Upvotes: 0

a man from another galaxy
a man from another galaxy

Reputation: 521

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).

Upvotes: 52

juergen d
juergen d

Reputation: 204746

Either use

SELECT IF(field1 IS NULL or field1 = '', 'empty', field1) as field1 
from tablename

or use the following code, which I copied from another answer (by Himanshu) to this same question, at https://stackoverflow.com/a/17833019/441757 —

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.

Upvotes: 253

christine
christine

Reputation: 51

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

Upvotes: 0

Sairam Krish
Sairam Krish

Reputation: 11681

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

Upvotes: 2

Gleb Kemarsky
Gleb Kemarsky

Reputation: 10398

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

Upvotes: 12

palhares
palhares

Reputation: 1823

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

Upvotes: 9

user7256715
user7256715

Reputation: 1

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

Upvotes: 0

Adam Noor
Adam Noor

Reputation: 15

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!";
}

Upvotes: -1

Himanshu
Himanshu

Reputation: 32602

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.

Upvotes: 17

Related Questions