【叶子函数分享四十三】SQL数字转英文函数
来源:互联网 发布:mysql查找替换字符串 编辑:程序博客网 时间:2024/04/30 10:42
--晴天兄(qianjin036a)的发帖地址:
--http://topic.csdn.net/u/20080614/12/d26adea8-ac05-4b06-8b8a-f46a4b564e3b.html
-- 数字转英文
-- =============================================
-- Author: qianjin036a
-- Create date:06/14/2008 02:27:17
-- Description:Arabic numerals to English
-- =============================================
go
--创建函数
CREATE FUNCTION Digit2English
(
@arabia decimal(38,17)
)
RETURNS varchar(1000)
AS
BEGIN
declare @atoe table(a int,e varchar(10))
insert into @atoe select 0,'zero' union all select 1,'one'
union all select 2,'two' union all select 3,'three'
union all select 4,'four' union all select 5,'five'
union all select 6,'six' union all select 7,'seven'
union all select 8,'eight' union all select 9,'nine'
declare @integer bigint,@trillion int,@billion int,@million int,@thousand int,@hundred int,@english varchar(1000)
select @integer=@arabia,@english=''
select @trillion=@integer % 1000000000000000/1000000000000,@billion=@integer % 1000000000000/1000000000,
@million=@integer % 1000000000/1000000,@thousand=(@integer % 1000000)/1000,@hundred=(@integer % 1000)
if @trillion>0
set @english=@english + dbo.ThreeDigit(@trillion) + 'trillion '
if @billion>0
set @english=@english + dbo.ThreeDigit(@billion) + 'billion '
if @million>0
set @english=@english + dbo.ThreeDigit(@million) + 'million '
if @thousand>0
set @english=@english + dbo.ThreeDigit(@thousand) + 'thousand '
if @hundred>0
set @english=@english + dbo.ThreeDigit(@hundred)
if @english=''
set @english='zero '
if @arabia-@integer>0.000000000
begin
declare @decimal decimal(18,17)
select @english=@english+'point ',@decimal=@arabia-@integer
while @decimal>0.0
begin
select @english=@english+e+' ' from @atoe where cast(@decimal*10 as int)=a
set @decimal=@decimal*10-cast(@decimal*10 as int)
end
end
return @english
END
GO
-- =============================================
-- Author: qianjin036a
-- Create date: 06/14/2008 02:27:17
-- Description: Three Digit Arabic numerals to English
-- =============================================
CREATE FUNCTION ThreeDigit
(
@integer int
)
RETURNS varchar(100)
WITH EXECUTE AS CALLER
AS
BEGIN
declare @atoe table(a int,e varchar(10))
insert into @atoe select 0,'zero' union all select 1,'one'
union all select 2,'two' union all select 3,'three'
union all select 4,'four' union all select 5,'five'
union all select 6,'six' union all select 7,'seven'
union all select 8,'eight' union all select 9,'nine'
union all select 10,'ten' union all select 11,'eleven'
union all select 12,'twelve' union all select 13,'thirteen'
union all select 14,'fourteen' union all select 15,'fifteen'
union all select 16,'sixteen' union all select 17,'seventeen'
union all select 18,'eighteen' union all select 19,'nineteen'
union all select 20,'twenty' union all select 30,'thirty'
union all select 40,'forty' union all select 50,'fifty'
union all select 60,'sixty' union all select 70,'severty'
union all select 80,'eighty' union all select 90,'ninety'
declare @english varchar(100)
set @english=''
if @integer>99
begin
select @english=e+' hundred ' from @atoe where @integer/100=a
set @integer=@integer % 100
if @integer>0
set @english=@english+'and '
end
if @integer<=20 and @integer>0
select @english=@english+e+' ' from @atoe where @integer=a
if @integer>20
begin
select @english=@english+e+' ' from @atoe where @integer/10*10=a
set @integer=@integer % 10
if @integer>0
select @english=@english+e+' ' from @atoe where @integer=a
end
RETURN @english
END
GO
select dbo.digit2english(123456789987654.321)
union all select dbo.digit2english(120045080045054.8412)
union all select dbo.digit2english(0.0102541)
go
/*
---------------------------------------------------------------------
one hundred and twenty three trillion four hundred and fifty six billion seven hundred and eighty nine million nine hundred and eighty seven thousand six hundred and fifty four point three two one
one hundred and twenty trillion forty five billion eighty million forty five thousand fifty four point eight four one two
zero point zero one zero two five four one
*/
- 【叶子函数分享四十三】SQL数字转英文函数
- 【叶子函数分享四十三】SQL数字转英文函数
- 【叶子函数分享五十五】数字转IP地址函数
- [MSSQL]SQL数字转英文函数
- [MSSQL]SQL数字转英文函数
- 【叶子函数分享四十七】获取指定索引的值的函数
- 【叶子函数分享二十九】完善SQL农历转换函数
- 【叶子函数分享三十】SQL简繁转换函数
- 【叶子函数分享四十一】SQL位移运算函数
- 【叶子函数分享二十九】完善SQL农历转换函数
- 数字转换成英文的SQL函数
- [MSSQL]SQL 数字转换英文函数
- 【叶子函数分享五十四】汉字转拼音函数
- 【叶子函数分享五十四】汉字转拼音函数
- 【叶子函数分享五十四】汉字转拼音函数
- 【叶子函数分享五十四】汉字转拼音函数
- 【叶子函数分享四十】将一个正整数分解为m个2的n次方的和
- 【叶子函数分享四十】将一个正整数分解为m个2的n次方的和
- ActionScript编译器
- 《COM技术内幕》图摘
- 从今天开始写博客了
- aswing组件知识点
- NetBeans 时事通讯(刊号 # 140 - Mar 22, 2011)
- 【叶子函数分享四十三】SQL数字转英文函数
- 利用Python抓取和解析网页(一)
- Win7的网络共享
- 【叶子函数分享四十四】全角半角转换函数
- 爱或者不爱
- 先写个symbian的吧 symbian中的.pkg文件详解
- 需要写一写了
- 以前代码无法运行的一系列问题
- Struts2的模型驱动、属性驱动的理解