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

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

2019-01-04

编程之家

https://www.jb51.cc

编程之家收集整理的这篇文章主要介绍了SQL货币数字转英文字符语句，编程之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

代码如下:

@H_403_3@Alter Function UDF_UtilConvertCurrencyToEnglish @H4033@( @H4033@@Money Numeric(15,2),@H4033@@Unit varchar(10)='BAHT' @H4033@) Returns Varchar(400) @H4033@As @H4033@/* @H4033@/// @H4033@/// Convert money to english @H4033@/// @H4033@/// e.g. 1234.56 @H4033@/// e.g. 'BAHT' @H4033@/// english money @H4033@*/ @H4033@Begin @H4033@DECLARE @result Varchar(400) @H4033@IF @Money=0 @H4033@Set @result= 'ZERO '+@Unit @H4033@Else @H4033@Begin @H4033@Declare @i Int,@hundreds Int,@tenth Int,@one Int,@thousand Int,@million Int,@billion Int,@numbers Varchar(400),@s Varchar(15) @H4033@Set @numbers='ONE TWO THREE FOUR FIVE ' @H4033@+'SIX SEVEN EIGHT NINE TEN ' @H4033@+'ELEVEN TWELEVE THIRTEEN FOURTEEN FIFTEEN ' @H4033@+'SIXTEEN SEVENTEEN EIGHTEEN NINETEEN ' @H4033@+'TWENTY THIRTY FORTY FIFTY ' @H4033@+'SIXTY SEVENTY EIGHTY NINETY ' Set @s=RIGHT('000000000000000'+Cast(@Money As varchar(15)),15) @H4033@Set @billion=Cast(Substring(@s,1,3) As Int) @H4033@Set @million=Cast(Substring(@s,4,3) As Int) @H4033@Set @thousand=Cast(Substring(@s,7,3) As Int) @H4033@Set @result='' @H4033@Set @i=0 While @i<=3 @H4033@BEGIN Set @hundreds=Cast(Substring(@s,@i*3+1,1) As Int) @H4033@Set @tenth=Cast(Substring(@s,@i*3+2,1) As Int) @H4033@Set @one=(Case @tenth When 1 Then 10 Else 0 End)+Cast(Substring(@s,@i*3+3,1) As Int) @H4033@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))) @H4033@Set @result=@result+' AND ' IF @hundreds>0 @H4033@Set @result=@result+RTRIM(Substring(@numbers,@hundreds*10-9,10))+' HUNDRED ' IF @tenth>=2 and @tenth<=9 @H4033@BEGIN @H4033@IF @hundreds>0 @H4033@Set @result=@result+' AND ' @H4033@Set @result=@result+RTRIM(Substring(@numbers,@tenth*10+171,10))+' ' @H4033@END IF @one>=1 and @one<=19 @H4033@BEGIN @H4033@IF @hundreds>0 AND @tenth=0 @H4033@Set @result=@result+' AND ' @H4033@Set @result=@result+RTRIM(Substring(@numbers,@one*10-9,10)) @H4033@END IF @i=0 and @billion>0 @H4033@Set @result=@result+' BILLION ' @H4033@IF @i=1 and @million>0 @H4033@Set @result=@result+' MILLION ' @H4033@IF @i=2 and @thousand>0 @H4033@Set @result=@result+' THOUSAND ' @H4033@Set @i=@i+1 @H4033@END IF(@result<>'') @H4033@Set @result=@result+' '+@Unit IF Substring(@s,14,2)<>'00' @H4033@Begin @H4033@Set @tenth=CAST(Substring(@s,1) AS INT) @H4033@Set @one=CAST(Substring(@s,15,1) AS INT) IF(@tenth>=2 and @tenth<=9) @H4033@Set @result=@result+RTRIM(Substring(@numbers,10)) @H4033@IF @tenth=1 AND @one>=1 and @one<=19 @H4033@Set @result=@result+' '+RTRIM(Substring(@numbers,CAST(Substring(@s,2) AS INT)*10-9,10)) @H4033@ELSE @H403_3@Set @result=@result+' '+RTRIM(Substring(@numbers,10)) SET @result=@result+' SatanG ' @H_4033@END @H4033@ELSE @H4033@Set @result=@result+' ONLY' END @H4033@RETURN @result @H4033@END@H403_3@