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

来源：互联网发布：组态软件有哪些编辑：程序博客网时间：2024/05/06 04:47

CREATE FUNCTION [dbo].[f_num_eng] (@num numeric(15,2))RETURNS varchar(400) WITH ENCRYPTIONASBEGIN--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

0 0