declare @amt1 decimal,@amt2 decimal,@s varchar(20) set @amt1=120.0 set @amt2=-120.0 set @s='00000000000000' -->14位 select right(@s+cast(cast(@amt1 as numeric(12,2)) as varchar),17) select case when @amt2<0 then '-' else '' end+right(@s+cast(cast(abs(@amt2) as numeric(12,2)) as varchar),17)/* 00000000000120.00 -00000000000120.00 */
使用REPLICATE产生0 然后加上去 组成字符串
DECLARE @i DECIMAL(12,2) SET @i=-120.00 SELECT CASE WHEN @i<0 THEN '-'+RIGHT(REPLICATE('0',17)+CAST(ABS(@i) AS VARCHAR(17)),16) ELSE RIGHT(REPLICATE('0',17)+CAST(@i AS VARCHAR(17)),17) END
set @amt1=120.0
set @amt2=-120.0
set @s='00000000000000' -->14位
select right(@s+cast(cast(@amt1 as numeric(12,2)) as varchar),17)
select case when @amt2<0 then '-' else '' end+right(@s+cast(cast(abs(@amt2) as numeric(12,2)) as varchar),17)/*
00000000000120.00
-00000000000120.00
*/
DECLARE @i DECIMAL(12,2)
SET @i=-120.00
SELECT
CASE
WHEN @i<0 THEN '-'+RIGHT(REPLICATE('0',17)+CAST(ABS(@i) AS VARCHAR(17)),16)
ELSE RIGHT(REPLICATE('0',17)+CAST(@i AS VARCHAR(17)),17)
END