Combining nullif while checking if field is an integer with a case statement

Question

As part of an ETL process, I need to copy a varchar field in table1 (staging table) which is meant to contain an integer value to a tinyint field in table2 (production table). However I am having some issues when trying to combine the following two business requirements:

1. If the value of the source varchar field is blank, then it will be set as NULL in the target tinyint field. For that I would normally use NULLIF.

2. If the field contains anything that could not be cast as a tinyint value (e.g. punctuation, letters, or a value higher than 255) then the value will be set as 255 in the target tinyint field. The rationale behind this is that the tinyint field would never have a 255 value unless there is an error -- and it wouldn't be appropriate to have errors set as NULL or 0 because these are perfectly valid values and we wouldn't be able to differentiate between a valid entry and a mistake.

The problem I have is trying to combine NULLIF with some sort of case statement. I think that perhaps this is not the right approach. Any thoughts on this?

Answer 1

I believe Azure supports TRY_CAST.

(Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.)

Here are three examples that seem to demonstrate what you're after:

--Column is blank, then NULL
DECLARE @YourCol1 VARCHAR(10) = ' '
SELECT CASE 
 WHEN @YourCol1 = ' '
 THEN NULL
 ELSE ISNULL(TRY_CAST(@YourCol1 AS TINYINT), 255)
 END AS YourCol1
--Column won't successfully CAST to TinyInt, then 255
DECLARE @YourCol2 VARCHAR(10) = 'abc'
SELECT CASE 
 WHEN @YourCol2 = ' '
 THEN NULL
 ELSE ISNULL(TRY_CAST(@YourCol2 AS TINYINT), 255)
 END AS YourCol2
--Column successfully CASTS to TinyInt, then actual value
DECLARE @YourCol3 VARCHAR(10) = '21'
SELECT CASE 
 WHEN @YourCol3 = ' '
 THEN NULL
 ELSE ISNULL(TRY_CAST(@YourCol3 AS TINYINT), 255)
 END AS YourCol3

• pmdci: Beautiful! thank you for that Scott. Sorry for the delay in replying, but I was away from the PC for the past days. All the best, P.
• Scott Hodgin - Retired: Glad it worked for you :)