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:
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.
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?