Can MySQL automatically convert empty strings to NULL?

MySQLMySQLi Database

You need to use NULLIF() function from MySQL. The syntax is as follows:

SELECT NULLIF(yourCoumnName,’ ’) as anyVariableName from yourTableName;

In the above syntax, if you compare empty string( ‘ ‘) to empty string( ‘ ‘), the result will always be NULL. However, if you compare with NULL to empty string( ‘ ‘) then also the result will always be NULL.

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table ConvertEmptyStringToNULL
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(20),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.63 sec)

Insert some records in the table using insert command. The query is as follows:

mysql> insert into ConvertEmptyStringToNULL(Name) values('John');
Query OK, 1 row affected (0.22 sec)
mysql> insert into ConvertEmptyStringToNULL(Name) values('');
Query OK, 1 row affected (0.15 sec)
mysql> insert into ConvertEmptyStringToNULL(Name) values(NULL);
Query OK, 1 row affected (0.14 sec)
mysql> insert into ConvertEmptyStringToNULL(Name) values('');
Query OK, 1 row affected (0.21 sec)
mysql> insert into ConvertEmptyStringToNULL(Name) values('Carol');
Query OK, 1 row affected (0.13 sec)
mysql> insert into ConvertEmptyStringToNULL(Name) values(NULL);
Query OK, 1 row affected (0.70 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from ConvertEmptyStringToNULL;

The following is the output:

+----+-------+
| Id | Name  |
+----+-------+
|  1 | John  |
|  2 |       |
|  3 | NULL  |
|  4 |       |
|  5 | Carol |
|  6 | NULL  |
+----+-------+
6 rows in set (0.00 sec)

Here is the query to convert empty string to NULL:

mysql> select NULLIF(Name,'') as EmptyStringNULL from ConvertEmptyStringToNULL;

The following is the output displaying NULL in place of empty string:

+------------------+
| EmptyStringNULL  |
+------------------+
| John             |
| NULL             |
| NULL             |
| NULL             |
| Carol            |
| NULL             |
+------------------+
6 rows in set (0.00 sec)
raja
Published on 26-Feb-2019 07:15:36
Advertisements