How to Replace an Empty String With NULL in MySQL Insert Query?

In this aricle we are going to see how we can insert NULL values in place of an empty string in MySQL/MariaDB. For the examples in this article, let's assume that:

1. We have a user table with three columns; id, name and job_title;
2. We have a variable $jobTitle (in any programming language) that can potentially have an empty string as value.

We'll be using the following build schema to build our table:

CREATE TABLE `user` (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(25) NOT NULL,
    job_title VARCHAR(25),
    PRIMARY KEY (id)
);

Using the NULLIF() Function

The NULLIF() function returns NULL if two expressions (for e.g. expr1 and expr2) are equal in comparison. If the two expressions are not equal, the first expression is returned. It has the following syntax:

# Return NULL if expr1 = expr2
NULLIF(expr1, expr2)

For example to insert NULL instead of an empty string using NULLIF(), we could do the following:

INSERT INTO `user` (`name`, `job_title`)
VALUES ('john', NULLIF('$jobTitle', ''));

This would insert NULL in the job_title column when the value of the variable "$jobTitle" matches an empty string. And, when the two expressions don't match, the value of the $jobTitle variable will be inserted.

Using the IF() Function

The IF() function returns the first expression if the condition is true and the second expression if the condition is false. It has the following syntax:

IF(condition, expr1, expr2)

We can simply have a condition to check if the value coming from a variable (e.g. "$jobTitle") is an empty string or not, and accordingly return NULL if the condition is true, or the string itself otherwise. For example:

INSERT INTO `user` (`name`, `job_title`)
VALUES ('john', IF('$jobTitle' = '', NULL, '$jobTitle'));

Using the CASE Operator

Using CASE could be another option. However, it may not be the best choice when you only have one condition. The syntax for it is as follows:

CASE
    WHEN condition1 THEN result1
    # ...
    WHEN conditionN THEN resultN
    ELSE result
END;

The following example shows how, using CASE, we can insert NULL when "$jobTitle" is empty, and insert the value of the variable itself otherwise:

INSERT INTO `user` (`name`, `job_title`)
VALUES (
    'john', (
        CASE
        WHEN '$jobTitle' = '' THEN NULL
        ELSE '$jobTitle'
        END
    )
);