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

这篇文章主要为大家详细介绍了SQL Server SQL货币数字转英文字符语句，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

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

注：关于SQL Server SQL货币数字转英文字符语句的内容就先介绍到这里，更多相关文章的可以留意