• SQLSERVER金额转换成英文大写的函数


    CREATE FUNCTION [dbo].[f_num_eng] (@num numeric(15,2))
    RETURNS varchar(400) WITH ENCRYPTION
    AS
    BEGIN
    --All rights reserved. pbsql
      DECLARE @i int,@hundreds int,@tenth int,@one int
      DECLARE @thousand int,@million int,@billion int
      DECLARE @numbers varchar(400),@s varchar(15),@result varchar(400)
      SET @numbers='one       two       three     four      five      '
                  +'six       seven     eight     nine      ten       '
                  +'eleven    twelve    thirteen  fourteen  fifteen   '
                  +'sixteen   seventeen eighteen  nineteen  '
                  +'twenty    thirty    forty     fifty     '
                  +'sixty     seventy   eighty    ninety    '
      SET @s=RIGHT('000000000000000'+CAST(@num AS varchar(15)),15)
      SET @billion=CAST(SUBSTRING(@s,1,3) AS int)--将12位整数分成4段:十亿、百万、千、百十个
      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)--百位0-9
        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)--个位0-19
        SET @tenth=(CASE WHEN @tenth<=1 THEN 0 ELSE @tenth END)--十位0、2-9
        IF (@i=1 and @billion>0 and (@million>0 or @thousand>0 or @hundreds>0)) or
           (@i=2 and (@billion>0 or @million>0) and (@thousand>0 or @hundreds>0)) or
           (@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds>0))
          SET @result=@result+', '--百位不是0则每段之间加连接符,
        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 '--百位是0则加连接符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 @tenth>0
            SET @result=@result+'-'
          ELSE
            IF @hundreds>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 SUBSTRING(@s,14,2)<>'00'
      BEGIN
        SET @result=@result+' point '
        IF SUBSTRING(@s,14,1)='0'
          SET @result=@result+'zero'
        ELSE
          SET @result=@result+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,14,1) AS int)*10-9,10))
        IF SUBSTRING(@s,15,1)<>'0'
          SET @result=@result+' '+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,15,1) AS int)*10-9,10))
      END
      RETURN(@result)
    END

    使用:

    select [dbo].[f_num_eng](233434.5)

    结果:

    two hundred and thirty-three thousand, four hundred and thirty-four point five




  • 相关阅读:
    Hibernate3.3 中常见错误
    Hibernate Tools for Eclipse插件的安装和使用
    写个换房(先卖后买)退个人所得税的攻略 (转)
    Unable to instantiate default tuplizer [org.hibernate.tuple.entity.PojoEntityTuplizer]
    用SQL删除重复记录的N种方法
    Spring中ref local与ref bean区别
    Nginx反向代理
    文件上传
    linux 进程
    pdo
  • 原文地址:https://www.cnblogs.com/fyq891014/p/4188758.html
Copyright © 2020-2023  润新知