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

王朝mssql·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航