SQL货币数字转英文字符语句
前端之家收集整理的这篇文章主要介绍了
SQL货币数字转英文字符语句，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
<div class="codetitle"><a style="CURSOR: pointer" data="58636" class="copybut" id="copybut58636" onclick="doCopy('code58636')">@H_301_1@ 代码如下:
<div class="codebody" id="code58636">
Alter Function UDF_Util_ConvertCurrencyToEnglish
(
@Money Numeric(15,2),
@Unit varchar(10)='BAHT'
) Returns Varchar(400)
As
/
///
/// Convert money to english
///
/// <param name="@Money">e.g. 1234.56
/// <param name="@Unit">e.g. 'BAHT'
/// english money
/
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,@i3+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,@hundreds10-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,@one10-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,1) AS INT)
Set @one=CAST(Substring(@s,15,1) AS INT) IF(@tenth>=2 and @tenth<=9)
Set @result=@result+RTRIM(Substring(@numbers,10))
IF @tenth=1 AND @one>=1 and @one<=19
Set @result=@result+' '+RTRIM(Substring(@numbers,CAST(Substring(@s,2) AS INT)*10-9,10))
ELSE
Set @result=@result+' '+RTRIM(Substring(@numbers,10)) SET @result=@result+' S
atanG '
END
ELSE
Set @result=@result+' ONLY' END
RETURN @result
END
