复制代码 代码如下:

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