How to handle null and NULL value in sql-CodePudding

So I have been facing this weird situation in mysql where somehow null values are inserted in my table. I'm talking about null value not NULL value.

I have attached image for better understating

as you can see name column have null and mobile_no have NULL

So after using this query

select Case when t1.name IS NULL then 'NA' 
            when t1.name= 'NA' or 'null' or NULL then 'NA' 
            else t1.name end as 'Name', 
       Case when t1.mobile_no IS NULL then 'NA' 
            when t1.mobile_no= 'NA' or 'null' or NULL then 'NA' 
            else t1.mobile_noend as 'Mobile no' from student;

after this I'm getting this result

|Name|Mobile no|
----------------
|null|NA       |

but I want below result

|Name|Mobile no|
----------------
|NA  |NA       |

CodePudding user response：

To compare a column with multiple values use col IN (x, y, z), not col = x OR y OR z.

You also can't compare with NULL using = or IN, so that has to be a separate check.

select 
    Case 
        when t1.name IS NULL OR t1.name IN ('NA' or 'null') then 'NA' 
        else t1.name 
    end as 'Name'

CodePudding user response：

Can you try use IN statement? That should work.

select Case WHEN (t1.name IN ('NA', 'null') OR t1.name IS NULL) THEN 'NA' else t1.name end as 'Name', 
Case WHEN (t1.mobile IN ('NA', 'null') OR t1.mobile IS NULL) THEN 'NA' else t1.mobile_no end as 'Mobile no' from student;