Select a column if other column is null

I need to select a field called ProgramID from a table and if the ProgramID is NULL then I need to select the value in the InterimProgramID from the same table and alias it as ProgramID.

How can I make a conditional SELECT statement to do this?

Answers


You need the ISNULL function.

SELECT ISNULL(a, b)

b gets selected if a is null.

Also, you can use the WHEN/THEN select option, lookup in BOL. Essentially: its c switch/case block meets SQL.


You can use either the ISNULL function or the COALESCE function. They both do pretty much the same thing, however ISNULL only takes two parameters and COALESCE takes multiple parameters (returning the first non-null it encounters). Both try the first param, then the second, (and COALESCE continues on)

DECLARE @IAMNULL VARCHAR
DECLARE @IAMNOTNULL VARCHAR
SET @IAMNOTNULL = 'NOT NULL'

SELECT ISNULL(@IAMNULL, @IAMNOTNULL)
--Output: 'NOT NULL'

DECLARE @IAMNULLALSO VARCHAR

SELECT COALESCE(@IAMNULL, @IAMNULLALSO, @IAMNOTNULL)
--Output: 'NOT NULL'

  select COALESCE ( ProgramID , InterimProgramID ) as 'ProgramID' 

SELECT ProgramID
  FROM a_table
 WHERE ProgramID IS NOT NULL
UNION
SELECT InterimProgramID AS ProgramID
  FROM a_table
 WHERE ProgramID IS NULL;

You can also use IFNULL function

select IFNULL(ProgramId,interimId) as ProgramId

Coalesce('zzz-' + ProgramID, InterimID) as programID will still ignore ProgramID even if you have a pretext value. It's a cool little function


Need Your Help

Converting integer to string?

python string integer

I want to convert an integer to a string in Python. I am typecasting it in vain:

button with no postback?

javascript asp.net html

Well i have a asp.net page where i have a button that im using to execute a JavaScript... but after the JavaScript has run the page reloads(postback) how can i avoid that?