數字轉換成大寫英文(感謝網上無名大蝦，我就隻要改編下就ok)

 

推荐一个我自己做的一个电影站,绝对没弹窗:-)  www.fastdy.com 速看电影吧,每天都更新,

最新的电影电视剧都在速看电影吧!~

 

 

CREATE Procedure dbo.FormatNumToEngCaps
@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+' and'
      Else
       SET @result=@result+' 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 '
END
GO

因為是為了報關服務，在加點小東西

 

CREATE   Procedure   dbo.INVOICE_Format
@num numeric(18,2),@i int
AS 
declare @tt varchar(500)
exec FormatNumToEngCaps  @num,@tt output

if ltrim(substring(@tt,1,3))='and'
    set @tt=substring(@tt,4,len(@tt))
if @i=0
 set  @tt ='US  DOLLARS '+ upper(@tt)+' ONLY'
else
 set  @tt =upper(@tt)+' CARTONS  ONLY'
 

CREATE TABLE #temp1  (
 [英文數字] [varchar] (500) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
)

insert into #temp1 values  (upper(@tt) )
select  * from #temp1
GO

ok.搞定，再次感謝無名大蝦共享資源!