CODEWITHSUNDEEP
mysqlsql
Vishwa Ratna
Vishwa Ratna

Reputation: 6420

Confusing behavior of ifnull() in SQL

I have this table 'customer_master' and some values in it.

CREATE TABLE customer_master(
    CUSTOMER_NUMBER VARCHAR(6),
    FIRSTNAME VARCHAR(30),
    middlename VARCHAR(30),
    lastname VARCHAR(30),
    CUSTOMER_CITY VARCHAR(15),
    CUSTOMER_CONTACT_NO VARCHAR(10),
    occupation VARCHAR(10),
    CUSTOMER_DATE_OF_BIRTH DATE,
    CONSTRAINT customer_custid_pk PRIMARY KEY (CUSTOMER_NUMBER)
);

I wanted to find customer_number,firstname, middlename and lastname fom the above table with condition that for those customers who dont have middle name then "null" value in middlename should be replaced by lastname.

So i thought why not use ifnull(middlename,lastname) here and when i used it then i got some interesting results.

Before using ifnull()

If i dont use ifnull then my result table looks like this.

select customer_number, firstname, middlename, lastname from customer_master;

enter image description here

After using ifnull()

select customer_number ,firstname, ifnull(middlename, lastname) as 'Middle_Name' , lastname from customer_master;

enter image description here

I expected that all null values in middlename will be replaced by lastname, but it did not happened why? and why only some middlename rows got the lastname values not all? Any explanation ?

http://sqlfiddle.com/#!9/2c742b/3

Upvotes: 4

Views: 155

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

If you imported the data, perhaps the NULL is really 'NULL', so try:

select . . .,
       (case when ltrim(rtrim(middlename)) = 'NULL' or middlename is null
             then lastname
             else middlename
        end) as middlename

Upvotes: 4

Related Questions