复制代码 代码如下: 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
复制代码 代码如下: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
