I'm creating an int field from a varchar field in a staging table in SQL Server 2012.
The field contains NULL values, positive and negative integers, and the text value "Unknown".
I would like to create a new int field and update it with the numeric values, set the text value of "Unknown" to 88888 and keep the NULL values as NULL.
I "tried" TRY_CAST but I really need the text "Unknown" set to the 88888 value because in this instance "Unknown" has a different meaning than NULL.
Any help will be greatly appreciated.