Checking for an empty field with MySQL

Question

I've wrote a query to check for users with certain criteria, one being they have an email address.

Our site will allow a user to have or not have an email address.

$aUsers=$this->readToArray('
 SELECT `userID` 
 FROM `users` 
 WHERE `userID` 
 IN(SELECT `userID`
         FROM `users_indvSettings`
  WHERE `indvSettingID`=5 AND `optionID`='.$time.')
  AND `email`!=""
 ');

Is this the best way to check for an empty field in SQL? I've just tried "IS NOT NULL" and this still returned a users record without them having an email address.

The query above works but out of curiosity I wondered if I'm doing it the correct way.

Quassnoi · Answer 1 · 2010-02-24T15:30:13.057

284

An empty field can be either an empty string or a NULL.

To handle both, use:

email > ''

which can benefit from the range access if you have lots of empty email record (both types) in your table.

edited Feb 24 '10 at 15:30

answered Feb 24 '10 at 15:20

Quassnoi

413,100
91
616
614

12

What is the inverse of this? (For when you want only NULL or '' fields) – Keylan Oct 26 '12 at 14:59
1

@Keylan: no single expression. – Quassnoi Oct 26 '12 at 15:45
3

@SEoF: this won't match `NULL` – Quassnoi Apr 16 '13 at 15:58
@Quassnoi - if " email > '' " matches both empty and null values, then the inverse is simply inverting the output of the boolean, which is " !(email > '') " as the " email > '' " bit is being evaluated first, and then the result is being inverted. Unless you are telling me the answer to the " email > '' " is either true, false or null... then yes, it won't work. – SEoF Apr 17 '13 at 09:48
Couldn't you just use the field `WHERE email` and then if null or empty will be handled as false – butterbrot Apr 29 '13 at 16:16
@butterbrot: any value which can't be cast into a non-zero integer will be handled as false – Quassnoi Apr 29 '13 at 16:49
2

I have same comment as Quassnoi's. I issue "select orig_id,hotline from normal_1952 where !(hotline > '')" and there's one record with null hotline but it doesn't match.I'm using MySQL 5.6 – Scott Chu Apr 15 '16 at 10:16
Yes! This won't match NULL. – Scott Chu Nov 25 '16 at 03:48
@SEoF - *"Unless you are telling me the answer to the " email > '' " is either true, false or null... then yes, it won't work."* Precisely - a boolean expression involving null doesn't return either true or false. (Which is how it should be in all languages, to avoid logic mistakes. One term for this is "tri-state logic".) – ToolmakerSteve Apr 14 '19 at 10:06

score 42 · Answer 2 · edited Jun 23 '17 at 09:28

42

Yes, what you are doing is correct. You are checking to make sure the email field is not an empty string. NULL means the data is missing. An empty string "" is a blank string with the length of 0.

You can add the null check also

AND (email != "" OR email IS NOT NULL)

edited Jun 23 '17 at 09:28

Nikolay Ivanov

5,159
1
26
22

answered Feb 24 '10 at 15:19

Yada

30,349
24
103
144

1

Your expression will match the empty strings as well. `OR email IS NOT NULL` is redundant here (it is implied by the previous condition). – Quassnoi Feb 24 '10 at 15:22
1

Interesting, this still returns records that have an empty `email` field. – Feb 24 '10 at 15:26
15

The OR should be an AND here. "OR email IS NOT NULL" will match a blank email string with length of 0. – pdavis Feb 24 '10 at 15:48
Notice pdavis comment "OR SHOULD BE AND" – beginner Jul 18 '17 at 08:42

Mathieu de Lorimier · Answer 3 · 2018-08-28T02:15:23.393

14

You could use

IFNULL(email, '') > ''

edited Aug 28 '18 at 02:15

answered Jul 08 '13 at 12:32

Mathieu de Lorimier

975
3
19
32

2

Great! And you dont need the TRIM. To invert, use IFNULL(email, '') = ''. – mauromartini Aug 18 '17 at 19:25

score 2 · Answer 4 · answered Feb 24 '10 at 15:20

2

There's a difference between an empty string (email != "") and NULL. NULL is null and an Empty string is something.

answered Feb 24 '10 at 15:20

Leslie

3,604
7
38
53

Which is why AND (email != "" OR email IS NOT NULL) is failing? – Feb 24 '10 at 15:27
3

should be `AND (email != '' AND email IS NOT NULL)` – thetaiko Feb 24 '10 at 15:30
@thetaiko: `email IS NOT NULL` is redundant here. `!=` predicate will never match a `NULL` value. – Quassnoi Feb 24 '10 at 15:34
what if you try: AND (trim(email) != '') – Leslie Feb 24 '10 at 15:35
I know I"m rather late to this discussion, but the statement will work if you reverse the order: "AND ((email IS NOT NULL) AND (email != '')). If evaluated in the other order, the statement evaluates as null (not TRUE) if the email address is NULL, and thus will not be either TRUE or FALSE. So the IS NULL check has to come FIRST! – Curt Jul 08 '13 at 04:10

score 2 · Answer 5 · answered Feb 24 '10 at 15:26

This will work but there is still the possibility of a null record being returned. Though you may be setting the email address to a string of length zero when you insert the record, you may still want to handle the case of a NULL email address getting into the system somehow.

     $aUsers=$this->readToArray('
     SELECT `userID` 
     FROM `users` 
     WHERE `userID` 
     IN(SELECT `userID`
               FROM `users_indvSettings`
               WHERE `indvSettingID`=5 AND `optionID`='.$time.')
     AND `email` != "" AND `email` IS NOT NULL
     ');

@op's query leaves no possibility of the `NULL` record returned. — Quassnoi, Feb 24 '10 at 15:40

score 1 · Answer 6 · answered Jun 26 '13 at 22:44

1

If you want to find all records that are not NULL, and either empty or have any number of spaces, this will work:

LIKE '%\ '

Make sure that there's a space after the backslash. More info here: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

answered Jun 26 '13 at 22:44

Scott Hildebrand

53
4

wondering if this also catches strings that consist only of newlines etc. – Rich Steinmetz Jan 23 '23 at 11:26

score 0 · Answer 7 · answered Mar 02 '23 at 10:36

0

WHERE TRIM(COALESCE(MyCol, '')) = ''

Query will return rows where MyCol is null or is any length of whitespace

See TRIM COALESCE and IS NULL for more info.

Also Working with null values from the MySQL docs

answered Mar 02 '23 at 10:36

christine

31
5

score -3 · Answer 8 · edited Sep 17 '16 at 13:38

-3

check this code for the problem:

$sql = "SELECT * FROM tablename WHERE condition";

$res = mysql_query($sql);

while ($row = mysql_fetch_assoc($res)) {

    foreach($row as $key => $field) {  

        echo "<br>";

        if(empty($row[$key])){

            echo $key." : empty field :"."<br>"; 

        }else{

        echo $key." =" . $field."<br>";     

        }
    }
}

edited Sep 17 '16 at 13:38

Muhammad Muazzam

2,810
6
33
62

answered Sep 17 '16 at 09:30

rahulcs754

39
1
1
5

There are better ways of doing this in query without having to loop through every single row – Jiho Kang Nov 29 '16 at 08:10

Checking for an empty field with MySQL

8 Answers8

check this code for the problem:

Linked