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:

    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:

    WHEN condition1 THEN result1
    # ...
    WHEN conditionN THEN resultN
    ELSE result

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`)
    'john', (
        WHEN '$jobTitle' = '' THEN NULL
        ELSE '$jobTitle'

This post was published by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.