SQL货币数字转英文字符语句

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