Problem with CAST
-
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
-
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?
-
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
-
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
-
Or you can eliminate the CASE altogether and do something like this
SELECT 'ST_GR' = RIGHT('0' + CONVERT(varchar(2),ST.GR),2)
-
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.
-
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.
-
I can imagine... but I'd rather not have any nightmares tonight
. -
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]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply