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
user
table with three columns;id
,name
andjob_title
; - 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
)
);
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.