复制代码 代码如下:
alter function udf_util_convertcurrencytoenglish
(
@money numeric(15,2),
@unit varchar(10)=’baht’
) returns varchar(400)
as
/*
/// <summary>
/// convert money to english
/// </summary>
/// <param name=”@money”>e.g. 1234.56 </param>
/// <param name=”@unit”>e.g. ‘baht’ </param>
/// <returns>english money</returns>
*/
begin
declare @result varchar(400)
if @money=0
set @result= ‘zero ‘+@unit
else
begin
declare @i int, @hundreds int, @tenth int, @one int, @thousand int,@million int,@billion int,@numbers varchar(400),@s varchar(15)
set @numbers=’one two three four five ‘
+’six seven eight nine ten ‘
+’eleven tweleve thirteen fourteen fifteen ‘
+’sixteen seventeen eighteen nineteen ‘
+’twenty thirty forty fifty ‘
+’sixty seventy eighty ninety ‘
set @s=right(‘000000000000000’+cast(@money as varchar(15)),15)
set @billion=cast(substring(@s,1,3) as int)
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)
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)
set @tenth=(case when @tenth<=1 then 0 else @tenth end)
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 ‘
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 @hundreds>0 and @tenth=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(@result<>”)
set @result=@result+’ ‘+@unit
if substring(@s,14,2)<>’00’
begin
set @tenth=cast(substring(@s,14,1) as int)
set @one=cast(substring(@s,15,1) as int)
if(@tenth>=2 and @tenth<=9)
set @result=@result+rtrim(substring(@numbers,@tenth*10+171,10))
if @tenth=1 and @one>=1 and @one<=19
set @result=@result+’ ‘+rtrim(substring(@numbers,cast(substring(@s,14,2) as int)*10-9,10))
else
set @result=@result+’ ‘+rtrim(substring(@numbers,@one*10-9,10))
set @result=@result+’ satang ‘
end
else
set @result=@result+’ only’
end
return @result
end