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