/*
运行环境:MS-SQL 2000
将小写金额转换为大写金额
整数部分最大值:9位数
小数部分最大值:2位
必须格式:整数.小数
状态:完成
Update date:-10-07
*/
/*
create procedure numTOchi
@num varchar(20)
as
*/
begin
DECLARE @NC VARCHAR(20)
SET @NC='2098765032.09'
declare @c1 varchar(30)
declare @c2 varchar(6)
declare @c3 varchar(20)
declare @zheng varchar (50)
declare @xiao varchar (50)
declare @num2chi varchar(50)
declare @chrNum varchar(50)
declare @i int
declare @zh int
declare @K int
declare @charindex varchar(2)
set @c1='千佰拾万千佰拾亿千佰拾万千佰拾元'
set @c2='角分'
set @c3='玖捌柒陆伍肆叁贰壹'
set @nc=LTRIM(Rtrim(@NC))
set @charindex=charindex('.',@nc)
if( @charindex!=0)
begin
-- set @zheng=convert(varchar(50),substring(@NC,1,(len(@nc)-3)))
-- set @xiao=convert(varchar(10),substring(@NC,(len(@zheng)+2),3))
set @zheng=substring(@nc,1,@charindex-1)
set @xiao=substring(@nc,@charindex+1,2)
end
else
begin
set @zheng= @nc-- substring(@nc,1,@charindex-1)
set @xiao=0
end
PRINT 'input money '+@NC
print '@zheng shu='+@zheng
print '@xiao shu='+@xiao
if(@zheng=0) and (@xiao=0)
begin
set @num2chi='零元整'
print @num2chi
end
else
begin
set @num2chi=''
if(@xiao!=0)
begin
--set @i=convert(int,len(@xiao))
set @i=len(@xiao)
while (0 begin
set @chrNum=convert(int,substring(@xiao,@i,1))
if (@chrNum!=0)
begin
set @num2chi=substring(@c2,@i,1)+@num2chi
set @num2chi=substring(@c3,(len(@c3)-@chrnum+1),1)+@num2chi
end
set @i=@i-1
end
end
set @K=0
if (@zheng!=0) --tranfesion zheng shu转换整数部分
begin
--set @i=convert(int,len(@zheng))
set @i=len(@zheng)
set @num2chi='元'+@num2chi
while(0 begin
begin
if((len(@zheng)-@i)=4)
set @num2chi='万'+@num2chi
else
begin
if(len(@zheng)-@i)=8
set @num2chi='亿'+@num2chi
end
end
-- begin
--- if (len(@zheng)-@zh)=12
--- set @num2chi='万'+@num2chi
--- end
set @chrnum=substring(@zheng,@i,1)
if (@chrnum!=0)
begin
if(@i=len(@zheng))
set @num2chi=substring(@c3,(len(@c3)-@chrnum+1),1)+@num2chi
else --- @i!=len(@zheng)
begin
if ((len(@zheng)-@i)!=4) and ((len(@zheng)-@i)!=8) and ((len(@zheng)-@i)!=12)and ((len(@zheng)-@i)!=16)
begin
if(right(@zheng,1)=0)
set @num2chi=substring(@c1,(len(@c1)-@k+1),1)+@num2chi --取千,佰 ('100000'本语句有效) 当个位数为零时有效
else --(right(@zheng,1)!=0)
set @num2chi=substring(@c1,(len(@c1)-@k),1)+@num2chi --取千,佰
end
set @num2chi=substring(@c3,(len(@c3)-@chrnum+1),1)+@num2chi --取大写数字
set @k=@k+1
end --- @i!=len(@zheng)
end -- (@chrnum!=0)
else -- chrnum=0
begin
if (substring(@num2chi,1,1)<>'元') and (substring(@num2chi,1,1)<>'万') and (substring(@num2chi,1,1)<>'亿')
begin
if(substring(@num2chi,1,1)<>'零')
set @num2chi='零'+@num2chi
end
set @k=@k+1
end -- else (@chrnum=0)
set @i=@i-1
end --- end if (@zheng!=0)
if( right(ltrim(@num2chi),1)<>'分')
begin
set @num2chi=@num2chi+'整'
end
end --?
end --?
--exec numTOchi 520001205.23
/*
运行结果如下: 在MS-SQL里怎样写1+2+3+....+50的循环语句
input money 1234567890.29
@zheng shu=1234567890
@xiao shu=29
@num2chi=壹拾贰亿叁千肆佰伍拾陆万柒千捌佰玖拾元贰角玖分
*/
来自 “ ITPUB博客 ” ，链接：/729024/viewspace-564834/，如需转载，请注明出处，否则将追究法律责任。
