--阿拉伯数字转中文大写数字
Create function dbo.NToCC(@n int)
returns varchar(2)
as
begin
declare @s varchar(2)
if @n=0
set @s='零'
if @n=1
set @s='壹'
if @n=2
set @s='贰'
if @n=3
set @s='叁'
if @n=4
set @s='肆'
if @n=5
set @s='伍'
if @n=6
set @s='陆'
if @n=7
set @s='柒'
if @n=8
set @s='捌'
if @n=9
set @s='玖'
return @s
end
go
--可支持到上亿
alter function dbo.numbertoChineseBigString(@m money)
returns varchar(255)
begin
declare @i varchar(255)
declare @f varchar(255)
declare @r varchar(255)
declare @s varchar(255)
set @s=convert(varchar(255),@m)
if charindex('.',@s)>0
begin
set @i=substring(@s,1,charindex('.',@s)-1)
set @f=substring(@s,charindex('.',@s)+1,len(@s))
end
else
set @i=@s
set @r=''
declare @k int
set @k=len(@i)
while @k>=1
begin
--最多9位
if @k=9
set @r=@r+
dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))
--@i
+'亿'
if @k=8
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+'仟'
if @k=7
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+'佰'
if @k=6
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+'拾'
if @k=5
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+'万'
if @k=4
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+'仟'
if @k=3
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+'佰'
if @k=2
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+'拾'
if @k=1
set @r=@r+dbo.NToCC(convert(int,substring(REVERSE(@i),@k,1)))+'圆'
set @k=@k-1
end
if convert(float,@f)=0
set @r=@r+'整'
else
begin
if substring(@s,1,1)>''
set @r=@r+dbo.NToCC(convert(int,substring(@f,1,1)))+'角'
if substring(@s,2,1)>''
set @r=@r+dbo.NToCC(convert(int,substring(@f,2,1)))+'分'
end
set @r=replace(@r,'零仟零佰零拾','零')
set @r=replace(@r,'零佰零拾','零')
set @r=replace(@r,'零仟零佰','零')
set @r=replace(@r,'零拾','零')
set @r=replace(@r,'零仟','零')
set @r=replace(@r,'零佰','零')
set @r=replace(@r,'拾零万','拾万')
set @r=replace(@r,'零零圆整','圆整')
set @r=replace(@r,'零圆整','圆整')
set @r=replace(@r,'零零万圆整','万圆整')
return @r
end