Alter Function Udf_util_convertcurrencytoenglish
(
@Money Numeric (15,2),
@Unit varchar (+) = ' BAHT '
) Returns Varchar (400)
As
/*
<summary>
Convert Money to 中文版
</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 (), @s Varc Har (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 ' 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
