--测试
create table #t(
万 char(1),
千 char(1),
百 char(1),
十 char(1),
元 char(1),
角 char(1),
分 char(1))declare @a numeric(19,2),@str char(7)
set @a=12.12
select @str=right(' $'+replace(cast(@a as varchar),'.',''),7)insert into #t select substring(@str,1,1),substring(@str,2,1),substring(@str,3,1),substring(@str,4,1),substring(@str,5,1)
,substring(@str,6,1),substring(@str,7,1)select * from #t
drop table #t
create table #t(
万 char(1),
千 char(1),
百 char(1),
十 char(1),
元 char(1),
角 char(1),
分 char(1))declare @a numeric(19,2),@str char(7)
set @a=12.12
select @str=right(' $'+replace(cast(@a as varchar),'.',''),7)insert into #t select substring(@str,1,1),substring(@str,2,1),substring(@str,3,1),substring(@str,4,1),substring(@str,5,1)
,substring(@str,6,1),substring(@str,7,1)select * from #t
drop table #t
万 char(1),
千 char(1),
百 char(1),
十 char(1),
元 char(1),
角 char(1),
分 char(1))
--创建存储过程
create procedure sp_new
@var varchar(1000)
AS
begin
declare @var1 varchar(50)
declare @i int
truncate table 表 while(charindex(',',@var)>0)
begin
set @var1 = substring(@var,1,charindex(',',@var)-1)
set @var = stuff(@var,1,charindex(',',@var),'')
print @var1
set @var1 = case when charindex('.',@var1)=0 then @var1 + '.00' else @var1 + '00' end
print @var1
set @var1 = left(@var1,charindex('.',@var1)+2) set @var1 = replace(@var1,'.','')
set @var1 = ' $' + @var1 print @var1 insert into 表 values(substring(@var1,len(@var1)-6,1),
substring(@var1,len(@var1)-5,1),
substring(@var1,len(@var1)-4,1),
substring(@var1,len(@var1)-3,1),
substring(@var1,len(@var1)-2,1),
substring(@var1,len(@var1)-1,1),
substring(@var1,len(@var1) ,1))
end
print @var
set @var = case when charindex('.',@var)=0 then @var + '.00' else @var + '00' end
set @var = left(@var,charindex('.',@var)+2)
set @var = replace(@var,'.','')
set @var = ' $' + @var
insert into 表 values(substring(@var,len(@var)-6,1),
substring(@var,len(@var)-5,1),
substring(@var,len(@var)-4,1),
substring(@var,len(@var)-3,1),
substring(@var,len(@var)-2,1),
substring(@var,len(@var)-1,1),
substring(@var,len(@var) ,1)) select * from 表
end go --执行
exec sp_new '2.0,20,200,2000,20000'drop table 表
A varchar(1) null,
B varchar(1) null,
C varchar(1) null,
D varchar(1) null,
E varchar(1) null )Create procedure PPtest19
@int int
as
insert into test19(A,B,C,D,E)
Select A=ISNULL((case when len(@int)=5 then substring(cast(@int as varchar(5)),1,1) when len(@int)=4 then '$' end),''),
B=ISNULL((case when len(@int)=4 then substring(cast(@int as varchar(4)),1,1) WHEN len(@int)>4 then substring(cast(@int as varchar(5)),2,1) when len(@int)=3 then '$' end),''),
C=ISNULL((case when len(@int)=3 then substring(cast(@int as varchar(3)),1,1) when len(@int)>3 then substring(right(cast(@int as varchar(5)),3),1,1) when len(@int)=2 then '$' end),''),
D=ISNULL((case when len(@int)=2 then substring(cast(@int as varchar(2)),1,1) when len(@int)>2 then substring(right(cast(@int as varchar(5)),2),1,1) when len(@int)=1 then '$' end),''),
B=(case when len(@int)=1 then substring(cast(@int as varchar(1)),1,1) when len(@int)>1 then right(cast(@int as varchar(5)),1) when len(@int)=0 then '' end)-------------執行過程 PPtest19 ----------
---- PPtest19 20
---- PPtest19 200
---- PPtest19 2000
---- PPtest19 20000
--------------結果--------------
A B C D E
$ 2 0
$ 2 0 0
$ 2 0 0 0
2 0 0 0 0
create table 表(万 char(1),千 char(1),百 char(1),十 char(1),元 char(1),
角 char(1),分 char(1))
--创建过程
Create procedure insertmoney
@money decimal (8, 2)
as
begin
DECLARE @money_str nvarchar(20)
SET @money_str=' $'+cast(cast(@money*100+0.5 as int) as nvarchar(10))
DECLARE @strlen int
SET @strlen=len(@money_str)
insert into 表 values
(
substring(@money_str,@strlen-6,1),
substring(@money_str,@strlen-5,1),
substring(@money_str,@strlen-4,1),
substring(@money_str,@strlen-3,1),
substring(@money_str,@strlen-2,1),
substring(@money_str,@strlen-1,1),
substring(@money_str,@strlen-0,1)
)
end--insertmoney 20
--insertmoney 20.29
--insertmoney 200
insertmoney 20000
SELECT * FROM 表
/*结果
$ 2 0 0 0
$ 2 0 2 9
$ 2 0 0 0 0
2 0 0 0 0 0 0
*/