How do I check if a column is empty or null in MySQL?

MySQLMySQLi Database

To check if a column is empty or null , we can use the where clause with IS NULL and for empty we can use the condition ‘ ‘ i.e. empty space. The steps required for this are as folllows: First a table is created with the help of create command as follows −

mysql> CREATE table ColumnValueNullDemo
-> (
-> ColumnName varchar(100)
-> );
Query OK, 0 rows affected (0.61 sec)

An empty value is inserted into the table using insert command. This is given below −

mysql> INSERT into ColumnValueNullDemo values(' ');
Query OK, 1 row affected (0.14 sec)

After that, the table records are displayed using the select command. This is given as follows −

mysql> SELECT * from ColumnValueNullDemo;

After executing the above query, we will get the following output −

+-------------------+
| ColumnName        |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

To check if the column has null value or empty, the syntax is as follows −

SELECT * FROM yourTableName WHERE yourSpecificColumnName IS NULL OR
yourSpecificColumnName = ' ';

The IS NULL constraint can be used whenever the column is empty and the symbol ( ‘ ‘) is used when there is empty value.

Now, the query using the above syntax is given as follows −

mysql> SELECT * FROM ColumnValueNullDemo WHERE ColumnName IS NULL OR
ColumnName = ' ';

After executing the above query, the output obtained is.

+------------+
| ColumnName |
+------------+
|            | 
+------------+
1 row in set (0.00 sec)

This output was obtained as the second condition is true for empty value.

Now, NULL value is inserted into the table with the help of the insert command as follows −

mysql> INSERT into ColumnValueNullDemo values();
Query OK, 1 row affected (0.14 sec)

The select command is used to view the contents of the table as follows −

mysql> SELECT * from ColumnValueNullDemo;

After executing the above query, the following output is obtained −

+------------+
| ColumnName |
+------------+
|            |
| NULL       |
+------------+
2 rows in set (0.00 sec)

Now, the condition to check for null or emptyis applied −

mysql> SELECT * FROM ColumnValueNullDemo WHERE ColumnName IS NULL OR
ColumnName = '';

Both the table rows are obtained as output because it is true in both conditions.

+------------+
| ColumnName |
+------------+
|            |
| NULL       |
+------------+
2 rows in set (0.00 sec)
raja
Published on 18-Oct-2018 15:53:44
Advertisements