cast in sql server 2005
i want to add 0 to a number
DECLARE @AccPD Char(6)
DECLARE @PostMT Char(2)
SELECT @accpd ='201102'
select @PostMT = case when substring(@accpd,5,2) = '01'
then substring(@accpd,5,2) + 11
else CAST('00' AS Char(2)) + substring(@accpd,5,2)-1
end
SELECT @P开发者_Go百科ostMT
this should return a value 01 instead of 1
DECLARE @Number INT
SET @Number = 9
Select '0' + CAST(@Number as VarChar(30))
Will output '09'
I would give more specific help but for the life of me I can't really understand what you're trying to accomplish here.
Additionally if you want to just pad a Int and return a string here's a function for that:
/*
Usage:
select dbo.fn_PadInt(3, '0', 5)
Returns '00003'
*/
CREATE FUNCTION fn_PadInt
(
@Input Int,
@PadCharacter Char(1),
@Length Int
)
RETURNS VarChar(20)
AS
BEGIN
DECLARE @InputLen INT
DECLARE @Output VarChar(20)
SET @InputLen = LEN(@Input)
SET @Output = CAST(@Input as VarChar(20))
WHILE @InputLen < @Length
BEGIN
SET @Output = @PadCharacter + @Output
SET @InputLen = @InputLen + 1
END
Return @Output
END
DECLARE @AccPD Char(6)
DECLARE @PostMT Char(2)
SELECT @accpd ='201102'
select @PostMT = case when substring(@accpd,5,2) = '01'
then right(substring(@accpd,5,2) + 11, 2)
else right(100 + substring(@accpd,5,2)-1, 2)
end
SELECT @PostMT
The problem was that when you use
then substring(@accpd,5,2) + 11
in the first branch of a CASE statement, the +11 produces an INT result, which also forces the 2nd branch to become an INT result (of 1). When the [INT] result is then assigned to the variable @PostMT Char(2), it is at that point that the INT (1) is converted to "1 " (note the space due to fixed-width char2)
Padding left is usually done with REPLICATE
DECLARE @AccPD Char(6)
DECLARE @PostMT Char(2)
SELECT @accpd ='201102'
select @PostMT = case when substring(@accpd,5,2) = '01'
then substring(@accpd,5,2) + 11
else substring(@accpd,5,2)-1
end
SET @PostMT = REPLICATE('0', 2 - LEN(@PostMT)) + @PostMT
SELECT @PostMT
精彩评论