Avatar of ttist25
ttist25

asked on 

Cast VARCHAR as INT keeping NULLs and setting text value to number

Good morning,

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.

Thanks!
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Ryan Chong
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of ttist25
ttist25

ASKER

Awesome!  THanks ste5an!
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

or

case
     when yourTextColumn  is null then null
     when yourTextColumn  = 'Unknown' then 88888
     else cast(yourTextColumn as int)
end

Open in new window


then values of yourTextColumn only contain Unknown, Nulll or integer values in varchar.

you need to tell us if there is exceptional cases, whereas the varchar values not equal to "Unknown"
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo