/***************************************************************************************************************/
CREATE Procedure stpMoneyFromNumberToEnglishDecimalFraction
@num numeric(18,2),
@result varchar(500) output
AS
BEGIN
DECLARE @i int,@hundreds int,@tenth int,@one int
DECLARE @thousand int,@million int,@billion int,@trillion int
DECLARE @numbers varchar(500),@s varchar(18)
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 '
Print @num
SET @s=RIGHT('000000000000000000'+CAST(@num AS varchar(18)),18)
SET @trillion=CAST(SUBSTRING(@s,1,3) AS int)
SET @billion=CAST(SUBSTRING(@s,4,3) AS int)
SET @million=CAST(SUBSTRING(@s,7,3) AS int)
SET @thousand=CAST(SUBSTRING(@s,10,3) AS int)
SET @result=''
SET @i=0
If @num <> 0
Begin
WHILE @i<=4
BEGIN
SET @hundreds=CAST(SUBSTRING(@s,@i*3+1,1) AS int)
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)
SET @tenth=(CASE WHEN @tenth<=1 THEN 0 ELSE @tenth END)
IF (@i=1 and @trillion>0 and (@billion>0 or @million>0 or @thousand>0 or @hundreds>0)) or
(@i=2 and (@trillion>0 or @billion>0) and (@million>0 or @thousand>0 or @hundreds>0)) or
(@i=3 and (@trillion>0 or @billion>0 or @million>0) and (@thousand>0 or @hundreds>0)) or
(@i=4 and (@trillion>0 or (@billion>0 or @million>0 or @thousand>0) and @hundreds>0))
SET @result=@result+', '
IF ((@i=3 or @i=4) and (@trillion>0 or @billion>0 or @million>0 or @thousand>0) and (@hundreds=0 and (@tenth>0 or @one>0)))
SET @result=@result+' 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 @trillion>0
SET @result=@result+' trillion'
IF @i=1 and @billion>0
SET @result=@result+' billion'
IF @i=2 and @million>0
SET @result=@result+' million'
IF @i=3 and @thousand>0
SET @result=@result+' thousand'
SET @i=@i+1
END
If SUBSTRING(@s,1,15) <> '000000000000000'
Begin
If @num > 1
SET @result=@result+' Dollars and'
Else
SET @result=@result+' Dollar and'
IF SUBSTRING(@s,17,2)<>'00'
BEGIN
IF SUBSTRING(@s,17,1)>='2' and SUBSTRING(@s,17,1)<='9'
BEGIN
SET @result=@result + ' ' +RTRIM(SUBSTRING(@numbers,SUBSTRING(@s,17,1)*10+171,10))
If SUBSTRING(@s,18,1)>='1' and SUBSTRING(@s,18,1)<='9'
SET @result=@result + '-' +RTRIM(SUBSTRING(@numbers,SUBSTRING(@s,18,1)*10-9,10))
END Else
IF SUBSTRING(@s,17,2)>='1' and SUBSTRING(@s,17,2)<='19'
BEGIN
SET @result=@result + ' ' +RTRIM(SUBSTRING(@numbers,SUBSTRING(@s,17,2)*10-9,10))
END
If (@num-Floor(@num))*100 > 1
SET @result=@result + ' Cents'
Else
SET @result=@result + ' Cent'
End else
Begin
SET @result=SUBSTRING(@result,0,len(@result)-3)
End
End Else
Begin
IF SUBSTRING(@s,17,1)>='2' and SUBSTRING(@s,17,1)<='9'
BEGIN
SET @result=@result + ' ' +RTRIM(SUBSTRING(@numbers,SUBSTRING(@s,17,1)*10+171,10))
If SUBSTRING(@s,18,1)>='1' and SUBSTRING(@s,18,1)<='9'
SET @result=@result + '-' +RTRIM(SUBSTRING(@numbers,SUBSTRING(@s,18,1)*10-9,10))
END Else
IF SUBSTRING(@s,17,2)>='1' and SUBSTRING(@s,17,2)<='19'
BEGIN
SET @result=@result + ' ' +RTRIM(SUBSTRING(@numbers,SUBSTRING(@s,17,2)*10-9,10))
END
If (@num-Floor(@num))*100 > 1
SET @result=@result + ' Cents'
Else
SET @result=@result + ' Cent'
End
Set @result=LTRIM(@result)
End Else
Set @result= 'Zero Dollar'
END
GO
--Drop Procedure fMoneyFromNumberToEnglishDecimalFraction
declare @a varchar(800)
Exec stpMoneyFromNumberToEnglishDecimalFraction 123.566 ,@a output
print @a
/***************************************************************************************************************/
CREATE FUNCTION [dbo].[f_num_eng] (@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