Problem with CAST

Question

Post reply

Problem with CAST

cdun2

SSCertifiable

Points: 7893
More actions
January 19, 2005 at 12:05 pm

#163164

Hello,
I have attempted the following CAST statement on a field called 'gr' which is of type SmallInt;
"ST_GR" = CASE
When Len(ST.GR) = 1 Then '0' + Cast(st.gr as varchar (2))
When Len(st.gr) > 1 Then ST.gr
End
This is supposed to add a zero in front of a number that is less than two digits, and just return the value of 'gr' otherwise.
Instead, none of my values that meet the condition of the first 'When' statement are concatenated with a leading zero. The second condition works fine.
What could the problem be?
Thank you for your help!
CSDunn

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

One way to do it would be this :

Select right('0' + cast(ST.GR as varchar(2)), 2)

PS a smallint can go up to 32 768 and your column seems to hold numbers that remain under 100... might I suggest that this column be changed to a tinyint?

cdun2 SSCertifiable Points: 7893 More actions · Answer 2

Thank you for the suggestion. right('0' + cast(ST.GR as varchar(2)), 2) works outside of the CASE, but not within the CASE. The same holds true for '0' + Cast(st.gr as varchar (2)).

CSDunn

cdun2 SSCertifiable Points: 7893 More actions · Answer 3

I've got it; I tested for one CASE with an ELSE condition, and CAST 'gr' for each;

"ST_GR" = CASE

When Len(ST.GR)=1 Then '0' + Cast(ST.GR as varchar)

Else CAST(ST.GR as varchar)

End

AlwaysLearning SSChasing Mays Points: 627 More actions · Answer 4

Or you can eliminate the CASE altogether and do something like this

SELECT 'ST_GR' = RIGHT('0' + CONVERT(varchar(2),ST.GR),2)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ya you just don't need a case in this situation. You can do it with a case but that's just wasted cpu cycles if you want my opinion.

AlwaysLearning SSChasing Mays Points: 627 More actions · Answer 6

Remi,

Just realized that I posted a nearly identical solution as yours

I'd also hate to to see what that case statement would turn into if they were padding a 10 digit number.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

I can imagine... but I'd rather not have any nightmares tonight .

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

FWIW. Here's another approach for the lazycoder:

SELECT REPLACE(STR(ST.GR,2),' ',0)

There is no need for a CASE here anyway.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]