将小写金额转换为英文大写的SQL函数

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
CREATEFUNCTION[dbo].[f_num_eng](@numnumeric(15,2))
RETURNSvarchar(400)WITHENCRYPTION
AS
BEGIN
--Allrightsreserved.pbSQL
 DECLARE@iint,@hundredsint,@tenthint,@oneint
 DECLARE@thousandint,@millionint,@billionint
 DECLARE@numbersvarchar(400),@svarchar(15),@resultvarchar(400)
 SET@numbers='one      two      three    four     five     '
             +'six      seven    eight    nine     ten      '
             +'eleven   twelve   thirteen fourteen fifteen  '
             +'sixteen  seventeeneighteen nineteen '
             +'twenty   thirty   forty    fifty    '
             +'sixty    seventy  eighty   ninety   '
 SET@s=RIGHT('000000000000000'+CAST(@numASvarchar(15)),15)
 SET@billion=CAST(SUBSTRING(@s,1,3)ASint)--将12位整数分成4段:十亿、百万、千、百十个
 SET@million=CAST(SUBSTRING(@s,4,3)ASint)
 SET@thousand=CAST(SUBSTRING(@s,7,3)ASint)
 SET@result=''
 SET@i=0
 WHILE@i<=3
 BEGIN
   SET@hundreds=CAST(SUBSTRING(@s,@i*3+1,1)ASint)--百位0-9
   SET@tenth=CAST(SUBSTRING(@s,@i*3+2,1)ASint)
   SET@one=(CASE@tenthWHEN1THEN10ELSE0END)+CAST(SUBSTRING(@s,@i*3+3,1)ASint)--个位0-19
   SET@tenth=(CASEWHEN@tenth<=1THEN0ELSE@tenthEND)--十位0、2-9
   IF(@i=1and@billion>0and(@million>0or@thousand>0or@hundreds>0))or
      (@i=2and(@billion>0or@million>0)and(@thousand>0or@hundreds>0))or
      (@i=3and(@billion>0or@million>0or@thousand>0)and(@hundreds>0))
     SET@result=@result+','--百位不是0则每段之间加连接符,
   IF(@i=3and(@billion>0or@million>0or@thousand>0)and(@hundreds=0and(@tenth>0or@one>0)))
     SET@result=@result+'and'--百位是0则加连接符AND
   IF@hundreds>0
     SET@result=@result+RTRIM(SUBSTRING(@numbers,@hundreds*10-9,10))+'hundred'
   IF@tenth>=2and@tenth<=9
   BEGIN
     IF@hundreds>0
       SET@result=@result+'and'1 <script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
阅读更多
文章标签: sql numbers
个人分类: 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭