数字转换成英文大写的SQL函数

博客展示了一个将数字转换成英文大写的SQL函数。该函数支持到千亿计数,保留四位小数。通过将整数分成不同段,对百位、十位、个位分别处理,添加相应连接符和单位,还对小数位进行判断和处理,最后返回转换后的英文大写字符串。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

/*****************************************************************
**
**数字转换成英文大写
**Code CreateByAbandonship 2005.11.21
**
*****************************************************************/

CREATE FUNCTION [dbo].[F__NumberChangeIntoEnglishUpperCase]

(@num numeric(18,4))

returns varchar(400) --with encryption

as

begin
--declare @num numeric(17, 4) --支持到千亿计数,保留四位小数
declare @i int,@hundreds int,@tenth int,@one int
declare @thousand int,@million int,@billion int
declare @numbers varchar(400),@s varchar(18),@result varchar(400)

--set @num = '1231231231.00121'
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('000000000000000000'+cast(@num as varchar(17)),17)

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)

-- Print '@thousand:'+convert( nvarchar(10),@thousand )
-- Print '@million:'+convert( nvarchar(10),@million )
-- Print '@billion:'+convert( nvarchar(10), @billion )

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,4)<>'0000'
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+' zero '
else
set @result = @result+rtrim(substring(@numbers,cast(substring(@s,15,1) as int)*10-9, 10))
/*判断小数点后第三位数字是否为零*/
if substring(@s,16,1)='0'
set @result = @result+' zero '
else
set @result = @result+rtrim(substring(@numbers,cast(substring(@s,16,1) as int)*10-9, 10))
/*判断小数点后第四位数字是否为零*/
if substring(@s,17,1)<>'0'
set @result = @result+' '+rtrim(substring(@numbers,cast(substring(@s,17,1) as int)*10-9,10))
else
set @result = @result+' zero'
end

-- Print '总字符:'+@s
-- Print '大写:'+@result

return(@result)
end

--用法For example:
--select dbo.F__NumberChangeIntoEnglishUpperCase(数字)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值