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?


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) = ' '

        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'

        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'

        WHEN @YourCol3 = ' '
            THEN NULL
        ELSE ISNULL(TRY_CAST(@YourCol3 AS TINYINT), 255)
        END AS YourCol3
  • 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. – pmdci Nov 28 '16 at 12:01
  • Glad it worked for you :) – Scott Hodgin - Retired Nov 28 '16 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.