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

To check whether a field is null or empty in MySQL, use the IF() function in MySQL. The syntax is as follows −

SELECT IF(yourColumnName IS NULL or yourColumnName = '', 'NULLId', yourColumnName) as anyVariableName from yourTableName;

To understand the above syntax, let us create a table. The following is the query to create a table −

mysql> create table NullAndEmptyDemo
-1> (
-> Id varchar(200)
-> );
Query OK, 0 rows affected (0.66 sec)

Let us now insert records into the table with the help of insert command. The query to insert records in the table is as follows. We have added null and empty values as well −

mysql> insert into NullAndEmptyDemo values(NULL);
Query OK, 1 row affected (0.22 sec)
mysql> insert into NullAndEmptyDemo values('Smith123');
Query OK, 1 row affected (0.17 sec)
mysql> insert into NullAndEmptyDemo values(' ');
Query OK, 1 row affected (0.35 sec)
mysql> insert into NullAndEmptyDemo values('98765');
Query OK, 1 row affected (0.15 sec)
mysql> insert into NullAndEmptyDemo values(NULL);
Query OK, 1 row affected (0.10 sec)
mysql> insert into NullAndEmptyDemo values('Carol567');
Query OK, 1 row affected (0.14 sec)
mysql> insert into NullAndEmptyDemo values('');
Query OK, 1 row affected (0.13 sec)

Displaying all records with the help of select statement. The query is as follows −

mysql> select *from NullAndEmptyDemo;

The following is the output −

+----------+
| Id       |
+----------+
| NULL     |
| Smith123 |
|          |
| 98765    |
| NULL     |
| Carol567 |
|          |
+----------+
7 rows in set (0.00 sec)

Now you can apply the above syntax we discussed in the beginning to check whether the field is null or empty. The query is as follows −

mysql> SELECT IF(Id IS NULL or Id = '', 'NULLId', Id) as UpdatedColumnValue from NullAndEmptyDemo;

The following is the output that replace the values, if NULL or empty (“”) is found at first −

+--------------------+
| UpdatedColumnValue |
+--------------------+
| NULLId             |
| Smith123           |
| NULLId             |
| 98765              |
| NULLId             |
| Carol567           |
| NULLId             |
+--------------------+
7 rows in set (0.00 sec)