將數字轉成英文大寫的函數﹐誰能幫我修改一下啊?
/*通過下面的函數得到的結果是﹕
select dbo.f_num_eng1(16047.12)
SIXTEEN THOUSAND AND FORTY-SEVEN POINT ONE TWO
我想要的是﹕
SIXTEEN THOUSAND FORTY SEVEN AND CENTS TWELVE
即修改要求﹕
1﹑"-"去掉﹔
2﹑整數部分不要出現 AND﹔
3﹑有小數時用"AND CENTS TWELVE"表示
急﹗我沒有時間改﹐100分送上﹐請大家幫忙了。謝謝﹗
*/
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
select dbo.f_num_eng1(16047.12)
问题点数：100、回复次数：28Top
1 楼LouisXIV（夜游神）回复于 2006-07-02 15:42:17 得分 0
小数是.123怎么办？
hundred twenty-three??
不符合英语读法嘛Top
2 楼rockyljt（江濤）回复于 2006-07-02 15:48:25 得分 0
只有兩位小數的情況
現在不管合理不合理了﹐就是要求這樣改啊﹐沒辦法﹐用戶至上啊Top
3 楼rockyljt（江濤）回复于 2006-07-02 15:50:43 得分 0
小数是.123怎么办？
應該是﹕one handred threeTop
4 楼LouisXIV（夜游神）回复于 2006-07-02 15:50:59 得分 0
--貌似是我多心了，lz这个好像是货币，小数也就2位
--下面这个是最简单的改动，等下看看能不能改良一下
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+' '--百位是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+''
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+' '
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+' and cent '
IF SUBSTRING(@s,14,1)='0'
SET @result=@result+'zero'
ELSE
SET @result=@result+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,14,2) AS int)*10-9,10))
END
RETURN(@result)
END
GO
select dbo.f_num_eng1(16047.12)
/*
sixteen thousand forty seven and cent twelve
*/Top
5 楼wwh999（印钞机V2.0...开发中....）回复于 2006-07-02 15:53:27 得分 0
给个方案:
1>写上一个转换用的函数（无非是个...CASE...WHEN...之类的）返回一个文本
2>将输出结果字段与函数绑定
求人先求已。大家都没空，我还有一套软件等着写呢。写模块分析去了...Top
6 楼LouisXIV（夜游神）回复于 2006-07-02 15:58:24 得分 0
--再改一下
alter 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+' '--百位是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+''
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+' '
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
IF SUBSTRING(@s,14,2)='01'
SET @result=@result+' and cent one'
ELSE
begin
SET @result=@result+' and cents '
SET @result=@result+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,14,2) AS int)*10-9,10))
end
END
RETURN(@result)
END
GO
select dbo.f_num_eng1(16047.11)
select dbo.f_num_eng1(16047.01)
/*
sixteen thousand forty seven and cents eleven
sixteen thousand forty seven and cent one
逻辑部分没有改动，就是最后如果小数是01则显示and cent，否则则为and cents
不知是否需要
*/
Top
7 楼sxycgxj（云中客）回复于 2006-07-02 16:01:40 得分 0
关注Top
8 楼LouisXIV（夜游神）回复于 2006-07-02 16:02:57 得分 0
个人觉得这个函数写得比较啰嗦^^;Top
9 楼rockyljt（江濤）回复于 2006-07-02 16:12:11 得分 0
我剛剛回復的不對
只考慮2位小數的情況一行了
Top
10 楼rockyljt（江濤）回复于 2006-07-02 16:17:11 得分 0
謝謝
LouisXIV(夜游神)
馬上結貼Top
11 楼LouisXIV（夜游神）回复于 2006-07-02 16:23:15 得分 40
等下发个改良版的看看^^Top
12 楼wwh999（印钞机V2.0...开发中....）回复于 2006-07-02 16:30:04 得分 0
没什么看题,,呵呵...
原来是这样的，LZ把个改良版的放来出看看。我看了其中很多代码是可以精简的。在实现功能的同时，越短越好啊！Top
13 楼rockyljt（江濤）回复于 2006-07-02 16:43:56 得分 0
To:LouisXIV(夜游神)
下面的有問題﹐不對
IF SUBSTRING(@s,14,2) < >'00'
BEGIN
IF SUBSTRING(@s,14,2)='01'
SET @result=@result+' and cent one'
ELSE
begin
SET @result=@result+' and cents '
SET @result=@result+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,14,2) AS int)*10-9,10))
end
END
RETURN(@result)
END
GO
測試﹕
select dbo.f_num_eng1(16047.33)
thousand fifteen five and cents tyTop
14 楼rockyljt（江濤）回复于 2006-07-02 16:44:29 得分 0
好﹐期待中Top
15 楼LouisXIV（夜游神）回复于 2006-07-02 17:10:33 得分 0
--未经过完整测试，如果lz测试没有问题可以封装进函数
set nocount on
declare @num money
set @num=1121151.35
declare @numstr varchar(20)
declare @decimal varchar(2)
declare @outputstring varchar(8000)
declare @commacount int
declare @numbers varchar(400)
declare @numbers2 varchar(400)
declare @integer varchar(3)
select @numstr=reverse(convert(varchar(20),@num,1))+','
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 @numbers2=' thousand million billion'
select @decimal=reverse(substring(@numstr,1,2))
select @outputstring=case
when @decimal =1 then 'and cent one'
when @decimal between 2 and 19 then 'and cents '+substring(@numbers,(@decimal)*10-9,10)
when @decimal >=20 then 'and cents '+rtrim(substring(@numbers,left(@decimal,1)*10+171,10))+rtrim(substring(@numbers,right(@decimal,1)*10-9,10))
when @decimal =0 then '' end
set @numstr=stuff(@numstr,1,3,'')
set @commacount=0
while charindex(',',@numstr)<>0
begin
select @integer=right('000'+reverse(substring(@numstr,1,charindex(',',@numstr)-1)),3)
select @outputstring=case
when substring(@integer,2,1)='0' then ''
when substring(@integer,2,1)='1' then 'one hundred '
else rtrim(substring(@numbers,(substring(@integer,1,1))*10-9,10))+' hundred '
end
+case
when substring(@integer,2,2) between '01' and '19' then rtrim(substring(@numbers,(substring(@integer,2,2))*10-9,10))
when right(@integer,2) >='20' then rtrim(substring(@numbers,left(substring(@integer,2,2),1)*10+171,10))+rtrim(substring(@numbers,right(substring(@integer,2,2),1)*10-9,10))
when right(@integer,2) ='0' then '' end
+rtrim(substring(@numbers2,@commacount*10,10))+' '+@outputstring
set @numstr=stuff(@numstr,1,charindex(',',@numstr),'')
set @commacount=@commacount+1
end
select @outputstring
set nocount off
/*
one million one hundred twentyone thousand one hundred fiftyone and cents thirtyfive
*/Top
16 楼LouisXIV（夜游神）回复于 2006-07-02 17:12:39 得分 0
小数分开处理了，整数部分可以用循环来做（以千分号为标记）Top
17 楼rockyljt（江濤）回复于 2006-07-02 19:10:36 得分 0
twentyone 中間應該有空格﹕twenty oneTop
18 楼LouisXIV（夜游神）回复于 2006-07-02 19:27:39 得分 0
declare @num money
set @num=1121151.35
declare @numstr varchar(20)
declare @decimal varchar(2)
declare @outputstring varchar(8000)
declare @commacount int
declare @numbers varchar(400)
declare @numbers2 varchar(400)
declare @integer varchar(3)
select @numstr=reverse(convert(varchar(20),@num,1))+','
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 @numbers2=' thousand million billion'
select @decimal=reverse(substring(@numstr,1,2))
select @outputstring=case
when @decimal =1 then 'and cent one'
when @decimal between 2 and 19 then 'and cents '+substring(@numbers,(@decimal)*10-9,10)
when @decimal >=20 then 'and cents '+rtrim(substring(@numbers,left(@decimal,1)*10+171,10))+' '+rtrim(substring(@numbers,right(@decimal,1)*10-9,10))
when @decimal =0 then '' end
set @numstr=stuff(@numstr,1,3,'')
set @commacount=0
while charindex(',',@numstr)<>0
begin
select @integer=right('000'+reverse(substring(@numstr,1,charindex(',',@numstr)-1)),3)
select @outputstring=case
when substring(@integer,2,1)='0' then ''
when substring(@integer,2,1)='1' then 'one hundred '
else rtrim(substring(@numbers,(substring(@integer,1,1))*10-9,10))+' hundred '
end
+case
when substring(@integer,2,2) between '01' and '19' then rtrim(substring(@numbers,(substring(@integer,2,2))*10-9,10))
when right(@integer,2) >='20' then rtrim(substring(@numbers,left(substring(@integer,2,2),1)*10+171,10))+' '+rtrim(substring(@numbers,right(substring(@integer,2,2),1)*10-9,10))
when right(@integer,2) ='0' then '' end
+rtrim(substring(@numbers2,@commacount*10,10))+' '+@outputstring
set @numstr=stuff(@numstr,1,charindex(',',@numstr),'')
set @commacount=@commacount+1
end
select @outputstring
Top
19 楼yyj135（天地男儿）回复于 2006-07-03 14:10:01 得分 20
TO:LouisXIV(夜游神)
我如果將set @num=1121151.35
改為set @num=10000
則結果為空，請問是怎么回事？謝謝！Top
20 楼LouisXIV（夜游神）回复于 2006-07-03 14:23:03 得分 0
declare @num money
set @num=10000
declare @numstr varchar(20)
declare @decimal varchar(2)
declare @outputstring varchar(8000)
declare @commacount int
declare @numbers varchar(400)
declare @numbers2 varchar(400)
declare @integer varchar(3)
select @numstr=reverse(convert(varchar(20),@num,1))+','
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 @numbers2=' thousand million billion'
select @decimal=reverse(substring(@numstr,1,2))
select @outputstring=case
when @decimal =1 then 'and cent one'
when @decimal between 2 and 19 then 'and cents '+substring(@numbers,(@decimal)*10-9,10)
when @decimal >=20 then 'and cents '+rtrim(substring(@numbers,left(@decimal,1)*10+171,10))+' '+rtrim(substring(@numbers,right(@decimal,1)*10-9,10))
when @decimal =0 then '' end
set @numstr=stuff(@numstr,1,3,'')
set @commacount=0
while charindex(',',@numstr)<>0
begin
select @integer=right('000'+reverse(substring(@numstr,1,charindex(',',@numstr)-1)),3)
select @outputstring=case
when substring(@integer,1,1)='0' then ''
when substring(@integer,1,1)='1' then 'one hundred'
else rtrim(substring(@numbers,(substring(@integer,1,1))*10-9,10))+' hundred '
end
+case
when substring(@integer,2,2) between '01' and '19' then rtrim(substring(@numbers,(substring(@integer,2,2))*10-9,10))
when right(@integer,2) >='20' then rtrim(substring(@numbers,left(substring(@integer,2,2),1)*10+171,10))+' '+rtrim(substring(@numbers,right(substring(@integer,2,2),1)*10-9,10))
when right(@integer,2) ='00' then ''
end
+rtrim(substring(@numbers2,@commacount*10,10))+' '
+@outputstring
set @numstr=stuff(@numstr,1,charindex(',',@numstr),'')
set @commacount=@commacount+1
end
select @outputstring
--把校验用的'00'写成了'0'-_-Top
21 楼turenjie（拉倒）回复于 2006-07-03 14:41:00 得分 10
收藏学习中......................Top
22 楼yyj135（天地男儿）回复于 2006-07-03 14:46:22 得分 20
謝謝LouisXIV(夜游神)，下面我將代碼整理一下，已供大家共享，LouisXIV(夜游神)請不要怪我侵犯你的版權：
--DROP FUNCTION [dbo].[f_num_en]
--函數名稱：f_num_en
--用途：將數字轉換為英文
--作者：LouisXIV(夜游神)
--建立日期：2006/7/3
--示范：SELECT [dbo].[f_num_en] ( 12345.67)
CREATE FUNCTION [dbo].[f_num_en]
(@num money)
RETURNS VARCHAR(400)
AS
BEGIN
DECLARE @numstr VARCHAR(20)
DECLARE @decimal VARCHAR(2)
DECLARE @outputstring VARCHAR(8000)
DECLARE @commacount INT
DECLARE @numbers VARCHAR(400)
DECLARE @numbers2 VARCHAR(400)
DECLARE @integer VARCHAR(3)
SELECT @numstr = Reverse(Convert(VARCHAR(20),@num,1)) + ','
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 @numbers2 = ' thousand million billion'
SELECT @decimal = Reverse(Substring(@numstr,1,2))
SELECT @outputstring = CASE
WHEN @decimal = 1 THEN 'and cent one'
WHEN @decimal BETWEEN 2
AND 19 THEN 'and cents ' + Substring(@numbers,(@decimal) * 10 - 9,10)
WHEN @decimal >= 20 THEN 'and cents ' + Rtrim(Substring(@numbers,Left(@decimal,1) * 10 + 171,10)) + ' ' + Rtrim(Substring(@numbers,Right(@decimal,1) * 10 - 9,10))
WHEN @decimal = 0 THEN ''
END
SET @numstr = Stuff(@numstr,1,3,'')
SET @commacount = 0
WHILE charindex(',',@numstr) <> 0
BEGIN
SELECT @integer = Right('000' + Reverse(Substring(@numstr,1,Charindex(',',@numstr) - 1)),
3)
SELECT @outputstring = CASE
WHEN Substring(@integer,1,1) = '0' THEN ''
WHEN Substring(@integer,1,1) = '1' THEN 'one hundred'
ELSE Rtrim(Substring(@numbers,(Substring(@integer,1,1)) * 10 - 9,
10)) + ' hundred '
END + CASE
WHEN Substring(@integer,2,2) BETWEEN '01'
AND '19' THEN Rtrim(Substring(@numbers,(Substring(@integer,2,2)) * 10 - 9,
10))
WHEN Right(@integer,2) >= '20' THEN Rtrim(Substring(@numbers,Left(Substring(@integer,2,2),1) * 10 + 171,
10)) + ' ' + Rtrim(Substring(@numbers,Right(Substring(@integer,2,2),1) * 10 - 9,
10))
WHEN Right(@integer,2) = '00' THEN ''
END + Rtrim(Substring(@numbers2,@commacount * 10,10)) + ' ' + @outputstring
SET @numstr = Stuff(@numstr,1,Charindex(',',@numstr),'')
SET @commacount = @commacount + 1
END
RETURN @outputstring
END
Top
23 楼LouisXIV（夜游神）回复于 2006-07-03 14:57:42 得分 0
:)Top
24 楼yyj135（天地男儿）回复于 2006-07-05 08:40:10 得分 0
LZ怎么不結貼？Top
25 楼Haiwer（海阔天空）回复于 2006-07-05 08:56:34 得分 0
select dbo.f_num_en(159.08)
one hundredfifty nine and cents eight
应该
one hundred fifty nine and cents eight
Top
26 楼Haiwer（海阔天空）回复于 2006-07-05 09:01:43 得分 0
WHEN Substring(@integer,1,1) = '1' THEN 'one hundred'
--〉
WHEN Substring(@integer,1,1) = '1' THEN 'one hundred '
Top
27 楼zjdyzwx（十一月猪）回复于 2006-07-05 21:36:55 得分 10
alter proc p_num_eng1 @num numeric(15,2)
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=cast('one' as char(10)) + cast('two' as char(10)) +cast('three' as char(10)) +
cast('four' as char(10)) + cast('five' as char(10)) + cast('six' as char(10)) + cast('seven' as char(10))
+ cast('eight' as char(10)) + cast('nine' as char(10)) + cast('ten' as char(10)) + cast('eleven' as char(10)) +
cast('tweleve' as char(10)) + cast('thirteen' as char(10)) + cast('fourteen' as char(10))
+ cast('fifteen' as char(10)) + cast('sixteen' as char(10)) + cast('seventeen' as char(10)) + cast('eighteen' as char(10))
+cast('ninteen' as char(10))+cast('twenty' as char(10))+cast('thirty' as char(10)) +cast('forty' as char(10)) +
cast('fifty' as char(10)) + cast('sixty' as char(10)) +cast('seventy' as char(10)) + cast('eighty' as char(10)) + cast('ninety' as char(10))
print @numbers
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 ) and @hundreds>0) or
(@i=2 and (@billion>0 or @million>0) and (@thousand>0 and @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))) or
(@i=2 and (@billion>0 or @million>0) and (@thousand>0 and @hundreds=0)) or
(@i=1 and @billion>0 and ( @million>0 or @thousand>0) and @hundreds=0)
SET @result=@result+' and '--百位是0則加連接符AND
*/
IF @hundreds>0
SET @result=@result+RTRIM(SUBSTRING(@numbers,@hundreds*10-9,10))+' hundred' -- search begin bit
IF @tenth>=2 and @tenth<=9
BEGIN
IF @hundreds>0
SET @result=@result+' and '
SET @result=@result+RTRIM(SUBSTRING(@numbers,@tenth*10+19*10 - 9,10)) ---(18 是one -- ten,, eleven -- ninteen)
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+' And Cents '
/*
IF SUBSTRING(@s,14,1)='0'
SET @result=@result+'zero'
ELSE
*/
/*Modify ,13 is point*/
-- Begin
declare @ten1 int
declare @one1 int
set @ten1 = cast(substring(@s,14,1) as int)
set @one1 = cast(substring(@s,15,1) as int)
if @ten1 <= 1
SET @result=@result+ substring(@numbers ,(@ten1*10 + @one1)*10 - 9 ,10)
else
SET @result=@result+ substring(@numbers ,@ten1*10*10 - 9 ,10) + substring(@numbers ,@one1*10*10 - 9 ,10)
-- End
/*
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
Print @result
END
GOTop
28 楼zjdyzwx（十一月猪）回复于 2006-07-05 22:20:52 得分 0
上面的修改一下：）：
alter proc p_num_eng1 @num numeric(15,2)
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=cast('one' as char(10)) + cast('two' as char(10)) +cast('three' as char(10)) +
cast('four' as char(10)) + cast('five' as char(10)) + cast('six' as char(10)) + cast('seven' as char(10))
+ cast('eight' as char(10)) + cast('nine' as char(10)) + cast('ten' as char(10)) + cast('eleven' as char(10)) +
cast('tweleve' as char(10)) + cast('thirteen' as char(10)) + cast('fourteen' as char(10))
+ cast('fifteen' as char(10)) + cast('sixteen' as char(10)) + cast('seventeen' as char(10)) + cast('eighteen' as char(10))
+cast('ninteen' as char(10))+cast('twenty' as char(10))+cast('thirty' as char(10)) +cast('forty' as char(10)) +
cast('fifty' as char(10)) + cast('sixty' as char(10)) +cast('seventy' as char(10)) + cast('eighty' as char(10)) + cast('ninety' as char(10))
print @numbers
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 ) and @hundreds>0) or
(@i=2 and (@billion>0 or @million>0) and (@thousand>0 and @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))) or
(@i=2 and (@billion>0 or @million>0) and (@thousand>0 and @hundreds=0)) or
(@i=1 and @billion>0 and ( @million>0 or @thousand>0) and @hundreds=0)
SET @result=@result+' and '--百位是0則加連接符AND
*/
IF @hundreds>0
SET @result=@result+RTRIM(SUBSTRING(@numbers,@hundreds*10-9,10))+' hundred ' -- search begin bit
IF @tenth>=2 and @tenth<=9
BEGIN
--IF @hundreds>0
--SET @result=@result+' and '
SET @result=@result+RTRIM(SUBSTRING(@numbers,(@tenth - 1 )*10 + 19*10 - 9,10)) ---(18 是one -- ten,, eleven -- ninteen)
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+' And Cents '
/*
IF SUBSTRING(@s,14,1)='0'
SET @result=@result+'zero'
ELSE
*/
/*Modify ,13 is point*/
-- Begin
declare @ten1 int
declare @one1 int
set @ten1 = cast(substring(@s,14,1) as int)
set @one1 = cast(substring(@s,15,1) as int)
if @ten1 <= 1
SET @result=@result+ substring(@numbers ,(@ten1*10 + @one1)*10 - 9 ,10)
else
SET @result=@result+ substring(@numbers ,(@ten1 - 1 )*10 + 19*10 - 9 ,10) + substring(@numbers ,@one1*10 - 9 ,10)
-- End
--- @ten1 - 1 @ten从2开始
/*
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
Print @result
END
GOTop
