--阿拉伯數字轉中文大寫數字
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