CODEWITHSUNDEEP
sql
whitz11
whitz11

Reputation: 229

when one column null then get data from other column

hope you can help. Not showing the whole query because I don't think its required but want to try and get the query to see there is a null value in 'street' so then look in 'free address' and put the result in there. I'm guessing it involves a case expression or concat

Current Result

SELECT

IdNum
street
free address

from ID


IdNum          street                  free address
  1          stack over flow               null
  2             null                   stack exchange
  3           downing street               null

Required Result - note free address column doesn't need to be on display, just for purpose of demonstration.

 IdNum          street                    free address
   1         stack over flow                  null
   2         stack exchange                stack exchange
   3         downing street                   null

Many thanks for any help

Upvotes: 0

Views: 64

Answers (5)

RajatPatel
RajatPatel

Reputation: 11

You can use following if working with SQL Server

Select [IdNum], 
Case 
When [street] Is Null AND [free address] Is Not Null 
Then [free address] else [street] END As 'street',
[free address]
from ID

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

Use the COALESCE() function, which can replace a NULL value with an alternative you choose:

SELECT IdNum, COALESCE(street, `free address`) AS street, `free address`
FROM ID

Upvotes: 1

StephaneM
StephaneM

Reputation: 4899

Depending on the database you're using you can use ISNULL, NVL, IFNULL or COALESCE.

Pick the right one here: SQL NULL Functions at w3schools

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460108

You can use CASE, COALESCE or (in SQL-Server) ISNULL:

SELECT IdNum,
       CASE WHEN street IS NULL THEN free_address ELSE street END as street,
       free_address
FROM ID;

SELECT IdNum,
       COALESCE(street, free_address) as street,
       free_address
FROM ID;

SELECT IdNum,
       ISNULL(street, free_address) as street,
       free_address
FROM ID;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

The COALESCE() function does this:

SELECT IdNum,
       COALESCE(street, free_address) as street,
       free_address
FROM ID;

Note: ID is a strange name for a table and free address isn't a valid column name (because of the space).

COALESCE() is an ANSI standard function available in basically all databases.

Upvotes: 2

Related Questions