CODEWITHSUNDEEP
mysql
Brandon
Brandon

Reputation: 93

Doing a select limiting character length doesn't return null values

I have a table full of address records and I'm trying to do a select with CHAR_LENGTH limiter for each column, but whenever I have a null value in a column it returns 0 results. Here's my example:

SELECT *
FROM SubjectDetailsView
WHERE ShipInFlag IS NULL
    AND ZipCode IS NOT NULL
    AND CHAR_LENGTH(ZipCode) < 11
    AND CHAR_LENGTH(State) = 2
    AND Address1 IS NOT NULL
    AND CHAR_LENGTH(Address1) < 36
    AND CHAR_LENGTH(Address2) < 36;

My main issue is with Address2. Zipcode, State, and Address1 always have a value, so that's never been an issue, but Address2 often has a null value. I get a result when I have data in the Address2 field, but if it's null, I get 0 results.

So is CHAR_LENGTH only looking for a string (1-35 in my case), and ignores null values? If so, how do I get my query to return results, if Address2 can have null values and not null values while still being less than 36 characters?

Upvotes: 0

Views: 258

Answers (2)

HamzaNig
HamzaNig

Reputation: 1029

You can use IFNULL Function to make a string have length like empty :

SELECT * FROM SubjectDetailsView
WHERE ShipInFlag IS NULL
AND CHAR_LENGTH(IFNULL(ZipCode, 'empty')) < 11
AND CHAR_LENGTH(State) = 2
AND CHAR_LENGTH(IFNULL(Address1, 'empty')) < 36
AND CHAR_LENGTH(IFNULL(Address2, 'empty')) < 36;

Upvotes: 1

A. Colonna
A. Colonna

Reputation: 872

You have to test if address2 is null:

SELECT *
FROM SubjectDetailsView
WHERE ShipInFlag IS NULL
    AND ZipCode IS NOT NULL
    AND CHAR_LENGTH(ZipCode) < 11
    AND CHAR_LENGTH(State) = 2
    AND Address1 IS NOT NULL
    AND CHAR_LENGTH(Address1) < 36
    AND (Address2 IS NULL OR CHAR_LENGTH(Address2) < 36);

Upvotes: 1

Related Questions