--select dbo.syscash2cap1033(34500.00)
CREATE function dbo.syscash2cap1033
(@num numeric(15,2))
returns varchar(400)
as
begin
--all rights reserved. pbsql
declare @i int,@hundreds int,@tenth int,@one int
declare @thousand int,@million int,@billion int
declare @numbers varchar(400),@s varchar(15),@result varchar(400)
set @numbers='one two three four five '
+'six seven eight nine ten '
+'eleven twelve thirteen fourteen fifteen '
+'sixteen seventeen eighteen nineteen '
+'twenty thirty forty fifty '
+'sixty seventy eighty ninety '
set @s=right('000000000000000'+cast(@num as varchar(15)),15)
set @billion=cast(substring(@s,1,3) as int)--?12位整?分成4段：十?、百万、千、百十?
set @million=cast(substring(@s,4,3) as int)
set @thousand=cast(substring(@s,7,3) as int)
set @result=''
set @i=0
while @i<=3
begin
set @hundreds=cast(substring(@s,@i*3+1,1) as int)--百位0-9
set @tenth=cast(substring(@s,@i*3+2,1) as int)
set @one=(case @tenth when 1 then 10 else 0 end)+cast(substring(@s,@i*3+3,1) as int)--?位0-19
set @tenth=(case when @tenth<=1 then 0 else @tenth end)--十位0、2-9
if (@i=1 and @billion>0 and (@million>0 or @thousand>0 or @hundreds>0)) or
(@i=2 and (@billion>0 or @million>0) and (@thousand>0 or @hundreds>0)) or
(@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds>0))
set @result=@result+' '--百位不是0?每段之?加?接符, 把 ','替換成 ''
if (@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds=0 and (@tenth>0 or @one>0)))
set @result=@result+' and '--百位是0?加?接符and
if @hundreds>0
set @result=@result+rtrim(substring(@numbers,@hundreds*10-9,10))+' hundred'
if @tenth>=2 and @tenth<=9
begin
if @hundreds>0
set @result=@result+' and '
set @result=@result+rtrim(substring(@numbers,@tenth*10+171,10))
end
if @one>=1 and @one<=19
begin
if @tenth>0
set @result=@result+' ' --這個地方把 '-' 換成了空格
else
if @hundreds>0
set @result=@result + ' and '
set @result=@result+rtrim(substring(@numbers,@one*10-9,10))
end
if @i=0 and @billion>0
set @result=@result + ' billion'
if @i=1 and @million>0
set @result=@result + ' million'
if @i=2 and @thousand>0
set @result=@result + ' thousand'
set @i=@i+1
end
if substring(@s,14,2)<>'00'
begin
set @result=@result + ' and cents ' --point 改為 and
if substring(@s,14,1)='0'
set @result=@result+ 'zero'
else
set @result=@result+rtrim(substring(@numbers,cast(substring(@s,14,1) as int)*10 + 171, 10))
if substring(@s,15,1)<> '0'
set @result=@result+' '+rtrim(substring(@numbers,cast(substring(@s,15,1) as int)*10-9,10))
end
return(@result)
end
