將數字轉成英文大寫的函數﹐誰能幫我修改一下啊?

---涛声依旧--- 2006-07-02 03:35:42
/*通過下面的函數得到的結果是﹕
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)
...全文
563 28 打赏 收藏 转发到动态 举报
写回复
用AI写文章
28 条回复
切换为时间正序
请发表友善的回复…
发表回复
十一月猪 2006-07-05
  • 打赏
  • 举报
 回复
上面的修改一下：）：

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
GO
十一月猪 2006-07-05
  • 打赏
  • 举报
 回复
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
GO
昵称被占用了 2006-07-05
  • 打赏
  • 举报
 回复
WHEN Substring(@integer,1,1) = '1' THEN 'one hundred'

--〉
WHEN Substring(@integer,1,1) = '1' THEN 'one hundred '
昵称被占用了 2006-07-05
  • 打赏
  • 举报
 回复
select dbo.f_num_en(159.08)
one hundredfifty nine and cents eight

应该
one hundred fifty nine and cents eight
yyj135 2006-07-05
  • 打赏
  • 举报
 回复
LZ怎么不結貼？
LouisXIV 2006-07-03
  • 打赏
  • 举报
 回复
:)
yyj135 2006-07-03
  • 打赏
  • 举报
 回复
謝謝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





turenjie 2006-07-03
  • 打赏
  • 举报
 回复
收藏学习中......................
LouisXIV 2006-07-03
  • 打赏
  • 举报
 回复
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'-_-
yyj135 2006-07-03
  • 打赏
  • 举报
 回复
TO:LouisXIV(夜游神)
我如果將set @num=1121151.35
改為set @num=10000
則結果為空，請問是怎么回事？謝謝！
LouisXIV 2006-07-02
  • 打赏
  • 举报
 回复
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
---涛声依旧--- 2006-07-02
  • 打赏
  • 举报
 回复
twentyone 中間應該有空格﹕twenty one
LouisXIV 2006-07-02
  • 打赏
  • 举报
 回复
小数分开处理了，整数部分可以用循环来做（以千分号为标记）
LouisXIV 2006-07-02
  • 打赏
  • 举报
 回复
--未经过完整测试，如果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
*/
---涛声依旧--- 2006-07-02
  • 打赏
  • 举报
 回复
好﹐期待中
---涛声依旧--- 2006-07-02
  • 打赏
  • 举报
 回复
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 ty
wwh999 2006-07-02
  • 打赏
  • 举报
 回复
没什么看题,,呵呵...

原来是这样的，LZ把个改良版的放来出看看。我看了其中很多代码是可以精简的。在实现功能的同时，越短越好啊！
LouisXIV 2006-07-02
  • 打赏
  • 举报
 回复
等下发个改良版的看看^^
---涛声依旧--- 2006-07-02
  • 打赏
  • 举报
 回复
謝謝
LouisXIV(夜游神)
馬上結貼
---涛声依旧--- 2006-07-02
  • 打赏
  • 举报
 回复
我剛剛回復的不對

只考慮2位小數的情況一行了
加载更多回复（8）
VSTO Word Add-in 小写转大写金额
word中将输入的小写金额快速转换为大写金额,方法A:1、输入所需要的阿拉伯数字，并选中；2、单击插入----编号按钮；3、弹出编号对话框，在编号类型列表中，选择"壹，贰，叁……"选项即可；4、单击"确定"按钮，即可实现将阿拉伯数字转化为大写金额形式。(但似乎并不是偶需要的格式内容,比如萬,圆,并被限制在1~999999)方法B:使用该源码插件,可自行修改.
反函数计算器——万能计算器
找了很久很久，终于找了。能求反正弦，反余弦，反正切 以及他们的图形。能将复杂的算式输入，回车键输出结果 结果算式均可复制，还可以将已经算出结果的算式修改重新算出结果。 温馨提醒：操作有点麻烦，有帮助，多看看，输错算式有提示，总之，坚持一下，学会了，就能体会到他的优秀。 另有一截图软件，也顺便给大家。可能报病毒，但绝对不是毒。 使用一段时间后，需要授传码。授传码为： 任意大写字母或者数字
HW-Unit03
03 JavaScript：密码生成器 客观的 该任务的目的是修改启动程序代码，以创建一个应用程序，使员工能够根据他们选择的条件生成随机密码。用户可以选择密码是否包含特殊字符，小写字母和数字字符。 过程 该问题已通过以下方式解决： 声明了函数生成密码 制作了每个大写字母的数组，还制作了（几乎）每个特殊字符的数组。对于小写，将函数.toLowerCase（）应用于大写数组，并使用数字字符Math.Random（） 声明了while循环，在每个循环中检查用户希望密码包含哪些内容，如果为true，则将其连接到密码。 由于密码最终大于所需的长度，因此需要包含一个中断。 局限性： 该解决方案有一个主要局限性：每个元素都以相同的模式添加（第一个特殊字符，然后是小写字母，然后是数字元素，然后是大写字母）并重复其自身 限制的解决方案： 为了解决该局限性，使用了随机函数，其中字符串密码中的元素被随机交换以消
计算机小写换大写函数,excel小写换大写函数的教程
在Excel录入金额时，为了贪图方便通常只录入阿拉伯数字，而没有录入大写的数字，这时候需要的事小写转化成大写的函数进行修改，下面是由学习啦小编分享的excel小写换大写函数的教程，希望对你有用。假设要转换的数字小写金额在A列，如图所示：excel小写换大写函数的教程1：选中要转换的数字所在的单元格区域，按下CTRL+1组合快捷键，弹出设置单元格格式对话框，如下图所示excel小写换大写函数的教程2...
计算机小写换大写函数,Excel函数公式应用：小写数字转换成人民币大写9种方法-excel技巧-电脑技巧收藏家...
Excel函数公式应用：小写数字转换成人民币大写9种方法方法1=IF(TRUNC(H16)=H16,TEXT(H16,"[DBNum2]G/通用格式")&"元整",TEXT(TRUNC(H16),"[DBNum2]G/通用格式"&"元"))&IF(AND(TRUNC(H16)<>H16,RIGHT(TRUNC(H16*10))<>"0"),TEXT(...
MS-SQL Server

34,588

社区成员

254,588

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章