在目前的物流软件中， 经常会遇到客户需要将金额信息转换为英文大写，通过SQL Server中的T-SQL语句编写函数实现如下： Create FUNCTION [dbo].[INV_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+’ CENTS ‘ 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
SET @result=@result +’ ONLY’ RETURN(@result) END
其他需求可在当前的基础上进行修改。
转载于:https://www.cnblogs.com/Gaojier/archive/2010/04/08/2783609.html
相关资源：Asp.net C# 数字货币金额转换为中英文金额写法