sql – 将数字转换为单词 – 第一,第二,第三等

create functION fnIntegerToWords(@number as BIGint) 
    RETURNS VARCHAR(1024)
AS

BEGIN
      DECLARE @Below20 TABLE (ID int identity(0,1),Word varchar(32))
      DECLARE @Below100 TABLE (ID int identity(2,Word varchar(32))
      INSERT @Below20 (Word) VALUES 
                        ( 'Zero'),('One'),( 'Two' ),( 'Three'),( 'Four' ),( 'Five' ),( 'Six' ),( 'Seven' ),( 'Eight'),( 'Nine'),( 'Ten'),( 'Eleven' ),( 'Twelve' ),( 'Thirteen' ),( 'Fourteen'),( 'Fifteen' ),('Sixteen' ),( 'Seventeen'),('Eighteen' ),( 'Nineteen' ) 

       INSERT @Below100 VALUES ('Twenty'),('Thirty'),('Forty'),('Fifty'),('Sixty'),('Seventy'),('Eighty'),('Ninety')

    declare @belowHundred as varchar(126) 

    if @number > 99 begin
        SELEct @belowHundred = dbo.fnIntegerToWords( @number % 100)
    end

    DECLARE @English varchar(1024) = 

    (

      SELECT Case 
        WHEN @number = 0 THEN  ''

        WHEN @number betweeN 1 AND 19 
          THEN (SELECT Word FROM @Below20 WHERE ID=@number)

       WHEN @number betweeN 20 AND 99   
         THEN  (SELECT Word FROM @Below100 WHERE ID=@number/10)+ '-' +
               dbo.fnIntegerToWords( @number % 10) 

       WHEN @number betweeN 100 AND 999   
         THEN  (dbo.fnIntegerToWords( @number / 100)) +' Hundred '+
             Case WHEN @belowHundred <> '' THEN 'and ' + @belowHundred else @belowHundred end 

       WHEN @number betweeN 1000 AND 999999   
         THEN  (dbo.fnIntegerToWords( @number / 1000))+' Thousand '+
             dbo.fnIntegerToWords( @number % 1000)  

       WHEN @number betweeN 1000000 AND 999999999   
         THEN  (dbo.fnIntegerToWords( @number / 1000000))+' Million '+
             dbo.fnIntegerToWords( @number % 1000000) 

       WHEN @number betweeN 1000000000 AND 999999999999   
         THEN  (dbo.fnIntegerToWords( @number / 1000000000))+' Billion '+
             dbo.fnIntegerToWords( @number % 1000000000) 

            ELSE ' INVALID INPUT' END
    )

    SELECT @English = RTRIM(@English)

    SELECT @English = RTRIM(LEFT(@English,len(@English)-1))
        WHERE RIGHT(@English,1)='-'

    RETURN (@English)

END