CODEWITHSUNDEEP
sqlsql-server
300
300

Reputation: 1031

How to run SQL query to pull data from one of the three columns?

I have a database table "table_name1" in SQL Server 2012 and I am using SQL Server Management Studio version 10.

Data in table looks like:

colpkey col1    col2    col3
1234    AB1234  AB1234  
1265    BS5379  BS5379  BS5379
2323    WE7865  WE7865  WE7865
3267            WB7690  WB7690
6543    NULL    DH6583  
8798                    NULL
9403    BF6543          NULL
9856    BH7548  BH7548  BH7548

The query I used to create this table sample table:

create table table_name1 (
  colpkey bigint primary key,
  col1 varchar(10) ,
  col2 varchar(10),
  col3 varchar(10)
);

I want to write a query so it should pull two columns, "colpkey" and "col". The "col" should have value from "col1". If "col1" is NULL or blank then it should get value from "col2". If "col2" is NULL or blank then it should get value from "col3". And if all "col1", "col2" and "col3" are either blank or NULL then value put in "col" should be blank ('').

So for the given sample "table_name1" the query should pull data like:

colpkey col
1234    AB1234
1265    BS5379
2323    WE7865
3267    WB7690
6543    DH6583
8798    
9403    BF6543
9856    BH7548

How can I write query to do this? I was trying to use CASE and came up with following query:

select colpkey, 
Case WHEN (col1 != null and col1!= '') then col1
     ELSE 
     (CASE WHEN (col2 != null and col2!= '') then col2
     ELSE
     (CASE WHEN (col3 != null and col3!= '') then col3
     ELSE '' END) END) END as col
from table_name1;

But it shows blank for each row, as:

colpkey col
1234    
1265    
2323    
3267    
6543    
8798    
9403    
9856

Please suggest where I am going wrong with the query?

Upvotes: 1

Views: 141

Answers (2)

Tim Lehner
Tim Lehner

Reputation: 15251

I like to use the standard SQL coalesce function over SQL Server's proprietary isnull for finding non-null values, and we can also use nullif to satisfy the "blank" requirement:

select colpkey, coalesce(nullif(col1, ''), nullif(col2, ''), nullif(col3, ''), '') as col
from table_name1;

UPDATE:

To answer "where I am going wrong with the query?" and as mentioned by @ToddVrba, you shouldn't compare to null using standard comparison operators such as equals, less-than and such. It helps to think of null as "unknown" in relational database land, which means that comparing a known value to an unknown value always yields an unknown value, or null. You should compare to null by using is null or is not null.

Additionally, your case statement is overly complicated; you only need one case statement, as in:

select colpkey, 
case when col1 is not null and col1 != '' then col1
     when col2 is not null and col2 != '' then col2
     when col3 is not null and col3 != '' then col3
     else '' end as col
from table_name1;

Though I find the coalesce and nullif combo to be more concise but just as readable.

Upvotes: 2

SpellChucker
SpellChucker

Reputation: 450

In SQL, you can't compare NULL with !=. You must do IS NOT NULL.

Upvotes: 5

Related Questions