News

Copyright © 2008-2018 Paula DiTallo

Tag Cloud



How do I change the value of a column in a CASE statement and eliminate NULLs?

In t-sql/mssql, if you have a known NULL value  you can issue a statement like:

SELECT
  ISNULL(BirthDate,'1/1/1970 12:00:00 AM') as BirthDate
FROM Drviers

The ISNULL function is replacing any nulls found in the BirthDate column to a default value of 1/1/1970.

In other situations, you may not have just NULL values, but other possibilities as well. 

SELECT
 ISNULL(BirthDate,'1/1/1970 12:00:00 AM') as BirthDate,
 CASE
   WHEN BusinessPhone IS NULL OR BusinessPhone = ''
        THEN 'Unknown'
   ELSE BusinessPhone
 END as BusinessPhone
FROM Drviers

The case statement is evaluating the value found for the BusinessPhone column. If the value is NULL or set to '', then the default value is Unknown, else if the value isn't null or set to '', then retain the value for the  BusinessPhone column found.
Thursday, September 19, 2013 9:57 AM

Feedback

No comments posted yet.


Post A Comment
Title:
Name:
Email:
Comment:
Verification: