Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 26-Jun-2020
3K+ Views
Advertisements