Need Assistance incorporating CASE With CAST

Question

Need Assistance incorporating CASE With CAST

JC-3113

SSCrazy Eights

Points: 8371
More actions
August 3, 2009 at 3:06 pm

#212917

Hi Folks
I am trying to incorporate a CAST statment into my CASE statements and am not having any luck and do not know if that is even possible.
This is just a quick example of what I have and what i would like to see
My example has this:
DECLARE @TestVal INT
SET @TestVal = 3
SELECT
CASE @TestVal
WHEN 1 THEN 'First One'
WHEN 2 THEN 'Second One'
WHEN 3 THEN 'Third One'
ELSE 'Other'
END
go
Output:
----------
Third One
Notice there is no heading whch I need and cannot figure out how to get
Second Example:
I want to incorporate a CAST statement into the mix
so that it comes out with a heading name and under it the abbreviated value
Something like:
Value
-----
Third
Thanks
Jim

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

JC (8/3/2009)
Hi Folks
I am trying to incorporate a CAST statment into my CASE statements and am not having any luck and do not know if that is even possible.
This is just a quick example of what I have and what i would like to see
My example has this:
DECLARE @TestVal INT
SET @TestVal = 3
SELECT
CASE @TestVal
WHEN 1 THEN 'First One'
WHEN 2 THEN 'Second One'
WHEN 3 THEN 'Third One'
ELSE 'Other'
END
go
Output:
----------
Third One
Notice there is no heading whch I need and cannot figure out how to get
Second Example:
I want to incorporate a CAST statement into the mix
so that it comes out with a heading name and under it the abbreviated value
Something like:
Value
-----
Third
Thanks
Jim

Try this as you need a column alias:

DECLARE @TestVal INT

SET @TestVal = 3

SELECT

CASE @TestVal

WHEN 1 THEN 'First One'

WHEN 2 THEN 'Second One'

WHEN 3 THEN 'Third One'

ELSE 'Other'

END as Value

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 2

Hi Lynn

that will work 🙂

is there anyway to interject a CAST statement to manipulate the length of the value ?

Thanks

Jim

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Can you provide an example of what you want to do with the CAST? I would venture to say that you can do it, but I'm not sure what you want to do.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 4

Hi Jack

lets say my example is this:

DECLARE @TestVal INT

SET @TestVal = 3

SELECT

CASE @TestVal

WHEN 1 THEN 'First One XXXXXXXXXXXXXXXX'

WHEN 2 THEN 'Second One XXXXXXXXXXXXXXXX'

WHEN 3 THEN 'Third One XXXXXXXXXXXXXXXX'

ELSE 'Other'

END TestVal;

go

If i just wanted to see the first 10 characters of Testval, how could I incorporate a CAST statement like this one in the above sql ?

CAST (TestVal as varchar(10)) TestValExp

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

JC,

I wouldn't use CAST, I'd use either the LEFT() or SUBSTRING() function. This will work:

DECLARE @TestVal INT

SET @TestVal = 3

SELECT

LEFT(CASE @TestVal

WHEN 1 THEN 'First One XXXXXXXXXXXXXXXX'

WHEN 2 THEN 'Second One XXXXXXXXXXXXXXXX'

WHEN 3 THEN 'Third One XXXXXXXXXXXXXXXX'

ELSE 'Other'

END, 10) TestVal ;

--OR

SELECT

SUBSTRING(CASE @TestVal

WHEN 1 THEN 'First One XXXXXXXXXXXXXXXX'

WHEN 2 THEN 'Second One XXXXXXXXXXXXXXXX'

WHEN 3 THEN 'Third One XXXXXXXXXXXXXXXX'

ELSE 'Other'

END, 1, 10) TestVal ;

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 6

Thanks Jack

i would not have figureed that out especially when putting the LEFT statement on two lines

LEFT(CASE @TestVal

xx

END, 10) TestVal ;

is that the way all functions work with CASE statements ?

Thanks

jim

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

SQL Server doesn't care about white space so yeah you can do that with any function as long as the CASE or inner function returns the right data type for the outer function.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 8

Hi Jack

okay..

thanks

just seems peculiar

appreciate the assist

Jim