CODEWITHSUNDEEP
sqlsql-server
user1605665
user1605665

Reputation: 4151

How do i display different results in SQL if a column is null or not

I have a column (of type Date) that is either null or not null. I want to create a column that displays either "Completed" or "Not Completed" based on the column being null or not. I've looked around and could find IsNull() but that only allowed me to select one result.

I imagine a SQL statement like below but are unable to find the required syntax. 

select rowID, if(isNull(CompletedDate), "Yes", "No")
From Table

Sample data

| RowID | CompletedDate |
|   1   | 2015-12-01    |
|   2   |               |
|   3   | 2015-12-01    |

Intended result

| RowID | Completed|
|   1   | Yes      |
|   2   | No       |
|   3   | Yes      |

This is done using SQL Server

Upvotes: 0

Views: 328

Answers (4)

Asjal Rana
Asjal Rana

Reputation: 143

select rowID, if(isNull(CompletedDate), "Yes", "No")
From Table

Its a MySql syntax if(isNull(CompletedDate), "Yes", "No") but in SQL Server you can achieve by using CASE or IIF condition.

With CASE:

select 
    rowID, 
    CASE WHEN CompletedDate IS NULL THEN 'No'
         ELSE 'Yes'
    END as Completed

With IIF condition:

select 
    rowID, 
    IIF(CompletedDate IS NULL, 'No', 'Yes') as Completed

Upvotes: 1

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

In SELECT statement:

IF C THEN R                         = CASE WHEN C THEN R END
IF C THEN R1 ELSE R2                = CASE WHEN C THEN R1 ELSE R2 END
IF C1 THEN R1 ELSE IF C2 THEN R2... = CASE WHEN C1 THEN R1 WHEN C2 THEN R2... END

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

try with CASE https://msdn.microsoft.com/es-es/library/ms181765(v=sql.120).aspx

select rowID, CASE WHEN CompletedDate IS NULL THEN 'No'
                   ELSE 'Yes'
              END as Completed
From Table

Upvotes: 1

zedfoxus
zedfoxus

Reputation: 37059

You can write something like this:

select
  rowid,
  case when completeddate is null then 'No' else 'Yes' end as Completed
from tablename

Upvotes: 2

Related Questions