Select a column if other column is null

SqlSql ServerTsql

Sql Problem Overview

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?

Sql Solutions

Solution 1 - Sql

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.

Solution 2 - 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'

Solution 3 - Sql

  select COALESCE ( ProgramID , InterimProgramID ) as 'ProgramID'

Solution 4 - Sql

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

Solution 5 - Sql

There is also:

Select NVL(Column_A, Column_B) From 'schema'.'table_name'

The NVL( ) function is available in Oracle, and not in MySQL or SQL Server. This function is used to replace NULL value with another value. It is similar to the IFNULL Function in MySQL and the ISNULL Function in SQL Server.

https://www.1keydata.com/sql/sql-nvl.html

Solution 6 - Sql

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

Solution 7 - Sql

You can also use IFNULL function

select IFNULL(ProgramId,interimId) as ProgramId
Categories
Recommended Sql Solutions
Recommended Sql Server Solutions
Recommended Tsql Solutions

Previous Solution:

Syntax and Sample Usage of _Generic in C11

CGenericsC11

Next Solution:

manual language selection in an iOS-App (iPhone and iPad)

IosLocalizationSelectionAppsettings

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionRonald McDonaldView Question on Stackoverflow
Solution 1 - SqlPeter Aron ZentaiView Answer on Stackoverflow
Solution 2 - SqlJustin PihonyView Answer on Stackoverflow
Solution 3 - SqlpaparazzoView Answer on Stackoverflow
Solution 4 - SqlonedaywhenView Answer on Stackoverflow
Solution 5 - SqlBoi GView Answer on Stackoverflow
Solution 6 - Sqluser220934View Answer on Stackoverflow
Solution 7 - SqlSathish MView Answer on Stackoverflow