SQL Server SQL货币数字转英文字符语句
2022-11-12 09:54:59
内容摘要
这篇文章主要为大家详细介绍了SQL Server SQL货币数字转英文字符语句,具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!
代码如下:
Alter
文章正文
这篇文章主要为大家详细介绍了SQL Server SQL货币数字转英文字符语句,具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | <code>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 </code> |
注:关于SQL Server SQL货币数字转英文字符语句的内容就先介绍到这里,更多相关文章的可以留意
代码注释