3 7 Votes

MySQL: How to check if Field is NULL or EMPTY

Question by Guest | 10/06/2015 at 17:28

Does someone know any way in MySQL to check whether an arbitrary field of a table is either containing an empty value (empty string) or it is NULL?

I have tried some things with IFNULL and similar approaches, but I had no success. Is there any trick? What should I take into account?

ReplyPositiveNegativeDateVotes

Stefan Trost

Show Profile | Message
Avatar
12Best Answer
24 Votes

If you want to read out all data records from a table containing an empty string or NULL as value in one specific column, you can use the following query:

SELECT * FROM tab WHERE col IS NULL OR col = ''

With "IS NULL", you can check for NULL, with "= ''", you can check for an empty string.

If you are searching for all datasets where the field is not NULL and not empty, you can use the following query instead:

SELECT * FROM tab WHERE col IS NOT NULL AND col != ''

If you want to use an IF condition for it, you can write it like that, for example:

SELECT IF(col IS NULL OR col = '', 'empty', col) FROM tab

With this query, you are checking at each dataset whether "col" is NULL or empty and depending on the result of this condition, either the string "empty" is returned in the case that the condition is TRUE or the content of the column is returned if not.
12/06/2015 at 18:11

ReplyPositive Negative
-420 Votes

The following is more efficient for finding empty.

SELECT * FROM tab WHERE col > '';
05/01/2017 at 10:29

Positive Negative
-410 Votes

For "IF(col IS NULL OR col = '', 'empty', col)", I believe you could use COALESCE().
01/06/2017 at 00:47

Positive Negative
11 Vote

Use The IFNULL() function

1. SELECT IFNULL(1,0); -- returns 1

2. SELECT IFNULL('',1); -- returns ''

3. SELECT IFNULL(NULL,'IFNULL function'); -- returns IFNULL function
11/06/2019 at 18:21

ReplyPositive Negative
Reply

Related Topics

MySQL: Check for NULL
Question | 1 Answer

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own articles on askingbox.com. How to do.