/***************************************************************************************************************/
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