We help IT Professionals succeed at work.

Create NULL value within CAST(CASE)

Medium Priority
291 Views
Last Modified: 2012-06-27
I am importing data from multiple old tables into a new table and I have a field that is INT but if there are no values, I need it to be NULL.  How can I specify a NULL value in the following:

intAID =
     CAST(CASE strAInfo
                   WHEN 'blah blah blah' THEN '1'
                   WHEN 'hee hee hee' THEN '2'
                   WHEN 'nada nada nada' THEN '3'
                   ELSE 'NULL'
      END as int),
Comment
Watch Question

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
ELSE NULL
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
No need for cast
intAID = CASE strAInfo
                   WHEN 'blah blah blah' THEN 1
                   WHEN 'hee hee hee' THEN 2
                   WHEN 'nada nada nada' THEN 3
                   ELSE NULL
         END,

Open in new window

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
if you walk off the end of a case without finding a match, CASE should return NULL

CASE strAInfo
                   WHEN 'blah blah blah' THEN '1'
                   WHEN 'hee hee hee' THEN '2'
                   WHEN 'nada nada nada' THEN '3'                  
      END

Explore More ContentExplore courses, solutions, and other research materials related to this topic.