数字转成英文的存储过程
来源:互联网 发布:房地产策划工资 知乎 编辑:程序博客网 时间:2024/05/12 18:02
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
select dbo.F__NumberChangeIntoEnglishUpperCase(20098.9666)
CREATE FUNCTION [dbo].[f_num_eng1] (@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
GO
- 数字转成英文的存储过程
- 数字转成大写的存储过程
- DatePickerDialog的月份为英文转成数字
- Java 数字转成英文
- 数字转成用英文表示
- 【Python】将数字转成英文
- C#中将数字金额转成英文大写金额的函数
- Excel以文本存储的数字转成数字
- 自动将存储过程转成C#代码的过程[转]
- Oracle存储过程转成Sqlserver存储过程
- 将数字转成英文描述(sql函数方法)
- 正则表达式将数字金额转成中文和英文
- 用SQL SERVER 2000的存储过程实现数据转成XML
- 在SQL*PLUS下建立输出数字的存储过程
- 数字转换成大写金额的存储过程或函数
- 英文中数字的训练
- 报表中金额的数字转成大写
- 把中文表示的数字转成阿拉伯数字
- 在桌面设置各种图标
- 第一次上机实验成果
- 数字转成大写的存储过程
- HDU 1046 Gridland
- Java:对象的强、软、弱和虚引用
- 数字转成英文的存储过程
- 解决进入系统检测硬盘的问题
- CRC校验
- The journey is the reward
- 软件开发工具 飞机浏览 学习顺序一
- 100个乒乓球,两个人轮流取,每人每次最多取5个,最少取1个,如果甲先取,怎样才能取到第100个乒乓球?
- HDU 1047 Integer Inquiry
- Java Map接口简介
- SQL知识