if exists (select * from sysobjects where name = 'sp_convertmoney' and type = 'P')
drop procedure sp_convertmoney
go
create procedure sp_convertmoney
@convmoney numeric(12,2)
as
begin
set nocount on
create table #tmp0 --大小写对比表
(id numeric(2,0) identity not null,
digital char(1) not null,
dx char(2) not null,
insert into #tmp0(digital,dx) values('1','壹')
insert into #tmp0(digital,dx) values('2','贰')
insert into #tmp0(digital,dx) values('3','叁')
insert into #tmp0(digital,dx) values('4','肆')
insert into #tmp0(digital,dx) values('5','伍')
insert into #tmp0(digital,dx) values('6','陆')
insert into #tmp0(digital,dx) values('7','柒')
insert into #tmp0(digital,dx) values('8','捌')
insert into #tmp0(digital,dx) values('9','玖')
insert into #tmp0(digital,dx) values('0','零')
create table #tmp1(
id numeric(2,0) identity not null, --序列号码
pos1 integer not null, --数据位置号
num char(1) null , --待转换的数据
unit char(2) null , --单位
dx char(2) null --保存该数据的大写
)
insert into #tmp1 (pos1,unit) values(-2,'分')
insert into #tmp1 (pos1,unit) values(-1,'角')
insert into #tmp1 (pos1,unit) values(0,' ')
insert into #tmp1 (pos1,unit) values(1,'元')
insert into #tmp1 (pos1,unit) values(2,'拾')
insert into #tmp1 (pos1,unit) values(3,'佰')
insert into #tmp1 (pos1,unit) values(4,'仟')
insert into #tmp1 (pos1,unit) values(5,'万')
insert into #tmp1 (pos1,unit) values(6,'拾')
insert into #tmp1 (pos1,unit) values(7,'佰')
insert into #tmp1 (pos1,unit) values(8,'仟')
insert into #tmp1 (pos1,unit) values(9,'亿')
insert into #tmp1 (pos1,unit) values(10,'拾')
insert into #tmp1 (pos1,unit) values(11,'佰')
insert into #tmp1 (pos1,unit) values(12,'仟')
declare @strmoney varchar(15), --声明字符型变量保存输入金额
@revsstrmoney varchar(15), --逆序字符
@len0 smallint, --全部数字长度
@adig char(1), --存放单个数字
@i integer
select @i= 1
select @convmoney = convert(numeric(12,2),@convmoney) --强制转换为 numeric(12,2)格式
select @strmoney = convert(varchar(15),@convmoney)
select @revsstrmoney = reverse(@strmoney)
select @len0 = datalength(@strmoney)
while @i<=@len0
begin
select @adig = substring(@revsstrmoney,@i,1)
update #tmp1 set num = @adig where id = @i
select @i = @i+1
end
update #tmp1 set dx = b.dx from #tmp1 a , #tmp0 b
where a.num = b.digital
/*
select id,num,dx,unit,dx+unit as dx_unit from #tmp1 where num>='0' and num<='9'
order by id desc
*/
declare cursor1 cursor for
select dx+unit as dx_unit from #tmp1 where num>='0' and num<='9'
order by id desc for read only
open cursor1
fetch cursor1 into @dx_unit
while @@SQLSTATUS =0
begin
select @result = @result +ltrim(rtrim(@dx_unit))
fetch cursor1 into @dx_unit
end
close cursor1
DEALLOCATE cursor cursor1
--select @result
while(charindex('零亿',@result)>0)
begin
select @result = stuff(@result,charindex('零亿',@result) ,4,'亿')
end
while(charindex('零万',@result)>0)
begin
select @result = stuff(@result,charindex('零万',@result) ,4,'万')
end
while(charindex('零仟',@result)>0)
begin
select @result = stuff(@result,charindex('零仟',@result) ,4,'零')
end
while(charindex('零佰',@result)>0)
begin
select @result = stuff(@result,charindex('零佰',@result) ,4,'零')
end
while(charindex('零拾',@result)>0)
begin
select @result = stuff(@result,charindex('零拾',@result) ,4,'零')
end
while(charindex('零元',@result)>0)
begin
select @result = stuff(@result,charindex('零元',@result) ,4,'元')
end
while(charindex('零万',@result)>0)
begin
select @result = stuff(@result,charindex('零万',@result) ,4,'万')
end
while(charindex('零角',@result)>0)
begin
select @result = stuff(@result,charindex('零角',@result) ,4,'零')
end
while(charindex('零分',@result)>0)
begin
select @result = stuff(@result,charindex('零分',@result) ,4,'零')
end
while(charindex('零零',@result)>0)
begin
select @result = stuff(@result,charindex('零零',@result) ,4,'零')
end
while(charindex('亿万',@result)>0)
begin
select @result = stuff(@result,charindex('亿万',@result) ,4,'亿')
end
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
