CODEWITHSUNDEEP
sqlsql-server
Prav
Prav

Reputation: 153

How to select value from second column if first column is blank/null in SQL (MS SQL)?

Can someone help me build a SQL query where if column1 is null/blank I should get value from column2, if column2 is also blank/null I should get value from column3.

Below is the table that I am using

Price1   Price2   Price3

120

          140

                 160

The output that I am looking for is

Price

120

140

160

I have already tried

select Price1 as Price
from A
WHERE PRICE1 IS NOT NULL
UNION
SELECT PRICE2 as Price
from A
where PRICE1 is null
UNION 
select PRICE3 as id
from A
where PRICE2 is null

select COALESCE (PRICE1,PRICE2,PRICE3) from A

select ISNULL(PRICE1,ISNULL(PRICE2,PRICE3)) from A

select 
case when PRICE1 IS not null then PRICE1 when PRICE1 IS null then PRICE2 WHEN PRICE2 IS NULL then PRICE3 end PRICE id from A

None of the above syntax gets the data I'm looking for. Please help

Upvotes: 7

Views: 8623

Answers (4)

Stephen Bodine
Stephen Bodine

Reputation: 519

Simple if null in first col, look in second, if that is null look in third col.

select isnull(PRICE1,isnull(PRICE2,PRICE3)) as Price from A

Upvotes: 0

ForguesR
ForguesR

Reputation: 3618

If you think you have empty strings you can try :

select 
  case when coalesce(PRICE1, '') <> '' then PRICE1 
       when coalesce(PRICE2, '') <> '' then PRICE2 
       when coalesce(PRICE3, '') <> '' then PRICE3 
  end AS PRICE 
FROM A

Upvotes: 1

Simone
Simone

Reputation: 1924

If your fields could be null or blank, you should check something like this:

select Price1 as Price
from A
WHERE PRICE1 IS NOT NULL AND PRICE1 != ''
UNION
SELECT PRICE2 as Price
from A
where PRICE1 is null OR PRICE1 = ''
UNION 
select PRICE3 as id
from A
where (PRICE1 is null OR PRICE1 = '') AND (PRICE2 is null OR PRICE2 = '')

Upvotes: 1

Aereaux
Aereaux

Reputation: 855

Use COALESCE like so:

SELECT COALESCE(Price1, Price2, Price3) FROM A;

However, this won't work if the entries are blank as opposed to NULL.

Upvotes: 11

Related Questions