Help with query - convert to int based on varchar value

Question

Post reply

Help with query - convert to int based on varchar value

cs_source

Default port

Points: 1459
More actions
November 29, 2012 at 7:50 am

#270858

Hi i'm new here to SQL Server Central and after lurking for a bit it seems like a great community. I'm here because TSQL is a new language for me and i'm reading some material however i would like to ask for some additional help on the following.
I have created a view and i would like to create a column (int) based on the value of a varchar for another field;
example
car = sedan or coupe
new_column = 1 if car = sedan
new_column = 0 if car = coupe
I believe the logic is there but i am just familiarizing myself with the syntax. Note that the purpose of this column is for something outside of SQL.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

You can use CASE WHEN:

SELECT ...

,CASE WHEN car = 'sedan' THEN 1

WHEN car = 'coupe' THEN 0

ELSE NULL -- or whatever you think appropriate

END AS [New_Column]

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 2

Hi,

You can do it with a CASE statement. I've included an example below.

DECLARE @Cars TABLE

(

BodyStyle VARCHAR(50)

)

INSERT INTO @Cars

SELECT 'Sedan'

UNION ALL SELECT 'Sedan'

UNION ALL SELECT 'Coupe'

SELECT

*

,CASE

WHEN BodyStyle = 'Sedan' THEN 1

WHEN BodyStyle = 'Coupe' THEN 0

END

FROM

@Cars

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

cs_source Default port Points: 1459 More actions · Answer 3

Thank you both Eugene Elutin and s_osborne2! works great!

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 4

No worries, glad it worked.

Thanks,

Simon

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne