写了个小写转大写的sp ,0.1 版本继续测试中
2006-04-09 23:07:12 来源:WEB开发网核心提示:--更换的最新版本 --另外临时表可以考虑改成永久表 if exists (select * from sysobjects where name = 'sp_convertmoney' and type = 'P') drop procedure sp_convertmoney go
--更换的最新版本
--另外临时表可以考虑改成永久表
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 @dx_unit varchar(12) ,@result varchar(255)
select @result = ''
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
if @convmoney = convert(numeric(12,0),@convmoney) --整数
select @result = substring( @result , 1 , charindex('元',@result)+1)+'整'
select @result= ltrim(rtrim(@result))
--解决只有小数的情况
select @result = ltrim(rtrim(@result))
if @result like '元%'
begin
select @result =substring( @result , 3 ,datalength(@result)-2)
select @result= ltrim(rtrim(@result))
end
--去第一个零
if @result like '零%'
begin
select @result = substring( @result , 3,datalength(@result)-2)
select @result = ltrim(rtrim(@result))
end
--去最后一个零
if @result like '%零'
begin
select @result = substring( @result , 1,datalength(@result)-2)
select @result = ltrim(rtrim(@result))
end
select @result
end
更多精彩
赞助商链接