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:
- We have a
usertable with three columns;
- 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) );
NULLIF() function returns
NULL if two expressions (for e.g.
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.
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'));
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 ) );
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.