CODEWITHSUNDEEP
mysqlsqlselect
Sam G
Sam G

Reputation: 337

Returning results that are not null in MySQL

I have a MySQL query like so:

SELECT `contact_last_name` FROM `customer` WHERE `contact_last_name` IS NOT NULL

Basically I want to return any rows where the column contact_last_name has something entered in it. If it makes any difference, this column is a VARCHAR column.

But instead, it is returning every row in the database, regardless of whether that column is empty or not!

I'm not sure why this query is not working?

Upvotes: 0

Views: 157

Answers (3)

Bohemian
Bohemian

Reputation: 425043

NULL (unknown value) is different to "blank" (known, but zero-length string), so you must check for both:

SELECT contact_last_name
FROM customer
WHERE contact_last_name IS NOT NULL
AND contact_last_name != ''

With mysql, this can be shortened to:

SELECT contact_last_name
FROM customer
WHERE ifnull(contact_last_name, '') != ''

Note also the removal of the unnecessary backticks from your query - none of the column/table names you have are reserved words.

Upvotes: 0

Neil Mukerji
Neil Mukerji

Reputation: 51

There's a difference between a varchar being an empty string and it being explicitly NULL. You could try this where clause:

LENGTH(contact_last_name) > 0;

Upvotes: 0

h2ooooooo
h2ooooooo

Reputation: 39542

NULL requires the column to actually be NULL. Not an empty string. Are you trying to check if it's empty?

SELECT 
    `contact_last_name` 
FROM 
    `customer` 
WHERE 
    `contact_last_name` != ''

Do you have white space issues?

SELECT 
    `contact_last_name` 
FROM 
    `customer` 
WHERE 
    TRIM(`contact_last_name`) != ''

Can it both be NULL and empty?

SELECT 
    `contact_last_name` 
FROM 
    `customer` 
WHERE 
    `contact_last_name` != '' 
AND 
    `contact_last_name` IS NOT NULL

Upvotes: 2

Related Questions