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?

295657 次浏览
小开
最佳答案

都可以

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

或者

SELECT case when field1 IS NULL or field1 = ''
then 'empty'
else field1
end as field1
from tablename

如果只想检查 null而不是空字符串，那么还可以使用 ifnull()或 coalesce(field1, 'empty')。但是这不适合于空字符串。

小开

或者你也可以使用 CASE:

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

如果您想检查 PHP，那么应该执行以下操作:

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

您可以创建一个函数来简化此操作。

create function IFEMPTY(s text, defaultValue text)
returns text deterministic
return if(s is null or s = '', defaultValue, s);

使用:

SELECT IFEMPTY(field1, 'empty') as field1
from tablename
小开

您可以在 IF中使用 IFNULL函数。这样会稍微短一点，字段名的重复次数也会少一些。

SELECT IF(IFNULL(field1, '') = '', 'empty', field1) AS field1
FROM tablename
小开

尝试使用 nullif:

SELECT ifnull(nullif(field1,''),'empty') AS field1
FROM tablename;
小开

通过修剪和比较，我们还确保在字段中处理空字符、制表符或空格字符内容。

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

提交答案