数字转英文(1200=> one thousand two HUNDRED)Sql函数
来源:互联网 发布:狮子数学脱口秀 知乎 编辑:程序博客网 时间:2024/05/19 12:16
CREATE function FunAmtEng(@Amt numeric(10,2))
RETURNS varchar(200)
as
begin
declare @strAmt varchar(10)
declare @str varchar(200)
declare @tempS varchar(200)
declare @i int
declare @j int
---------
set @str=''
set @i=1
set @j=1
set @strAmt=right(cast(round(10000000+@Amt,2) as varchar),9)
------
if cast(left(@strAmt,6) as int)>0
begin
while @j<3
begin
if (@j=1)
set @tempS=left(@strAmt,3)
else
set @tempS=substring(@strAmt,4,3)
---------------------------------------------------------------------
if cast(@tempS as int)>0
begin
while @i<3
begin
if @i=1
begin
set @str=dbo.FunFormatAmtEng(left(@tempS,1) ,@str ,1)
if cast(left(@tempS,1) as int)>0
set @str=@str+' HUNDRED AND'
end
else if @i=2
begin
if substring(@tempS,@i,1)='0'
set @str=dbo.FunFormatAmtEng(substring(@tempS,@i+1,1) ,@str ,1)
else if substring(@tempS,@i,1)='1'
set @str=dbo.FunFormatAmtEng(substring(@tempS,@i,2) ,@str ,2)
else begin
set @str=dbo.FunFormatAmtEng(substring(@tempS,@i,1) ,@str ,3)
set @str=dbo.FunFormatAmtEng(substring(@tempS,@i+1,1) ,@str ,1)
end
end
set @i=@i+1
end
end
---------------------------------------------------------------------
if (@j = 1) and (cast(@tempS as int) > 0)
set @str= @str + ' THOUSAND'
set @j=@j+1
set @i=1
end
set @str= @str + ' DOLLARS AND ' +right(@strAmt,2) + ' CENTS'
end
else
set @str= @str + ' ZERO DOLLARS AND ' +right(@strAmt,2) + ' CENTS'
------
return @str
end
===========================================================
CREATE function FunFormatAmtEng(@strAmt varchar(9),@str varchar(200),@fg int)
RETURNS varchar(200)
as
begin
set @str=@str+ case @fg when 1 then
case substring(@strAmt,1,1) when '1' then ' ONE'
when '2' then ' TWO'
when '3' then ' THREE'
when '4' then ' FOUR'
when '5' then ' FIVE'
when '6' then ' SIX'
when '7' then ' SEVEN'
when '8' then ' EIGHT'
when '9' then ' NINE'
else ''
end
-----------------------------------------------------------------------------------------------------------------
when 2 then
case substring(@strAmt,2,1) when '0' then ' TEN'
when '1' then ' ELEVEN'
when '2' then ' TWELVE'
when '3' then ' THIRTEEN'
when '4' then ' FOURTEEN'
when '5' then ' FIFTEEN'
when '6' then ' SIXTEEN'
when '7' then ' SEVENTEEN'
when '8' then ' EIGHTEEN'
when '9' then ' NINETEEN'
else ''
end
-----------------------------------------------------------------------------------------------------------------
when 3 then
case substring(@strAmt,1,1) when '2' then ' TWENTY'
when '3' then ' THIRTY'
when '4' then ' FORTY'
when '5' then ' FIFTY'
when '6' then ' SIXTY'
when '7' then ' SEVENTY'
when '8' then ' EIGHTY'
when '9' then ' NINETY'
else ''
end
-----------------------------------------------------------------------------------------------------------------
end
return isnull(@str,'')
end
调用:select dbo.FunAmtEng(1200)
- 数字转英文(1200=> one thousand two HUNDRED)Sql函数
- 程序员面试金典——解题总结: 9.17中等难题 17.7给定一个整数,打印该整数的英文描述(例如 "One Thousand,Two Hundred-Thirty Four")
- 1783 转化为one thousand and seven hundred and eighty -three
- [MSSQL]SQL数字转英文函数
- [MSSQL]SQL数字转英文函数
- sql数字转英文
- Lesson one hundred twenty five Tea for two.
- 【叶子函数分享四十三】SQL数字转英文函数
- 【叶子函数分享四十三】SQL数字转英文函数
- 数字转换成英文的SQL函数
- [MSSQL]SQL 数字转换英文函数
- hdu4374 One hundred layer
- hdu4374 One hundred layer
- Trainmaster One Hundred Twenty
- One hundred things
- hdu4374 One hundred layer
- One hundred years of uncertainty
- HDU 4374 - One hundred layer
- CSDN技术专家群工作流程
- 安装DotNetNuke
- Ubuntu安装使用系列: 目录和说明
- 曾用网名一览
- Ubuntu 设置指南
- 数字转英文(1200=> one thousand two HUNDRED)Sql函数
- 超简单!只需四步将照片处理成手工素描
- 用命令启动控制面板
- CSDN技术专家群 FAQ
- 程序员需做工作
- 哪位老大能找到ClearCase 7.0?
- web打印(调试通过)
- LDAP技术报告
- 项目用到请求分发