Convert number to words - first, second, third and so on

Question

Does anyone know how if it's possible to take an integer variable in SQL and convert it to the equivilent number of the form: First, Second, Third, Fourth etc?

Thanks

Answer 1

This seems like a simpler approach, a nice recursive algorithm

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,1), 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 

Answer 2

You need to use a CASE statement:

SELECT intfield CASE WHEN 1 THEN 'First'
                     WHEN 2 THEN 'Second'
                     WHEN 3 THEN 'Third'
                     ...other conversions...
                     ELSE '' END
FROM Mytable

EDIT:

Working code for 3 character numbers below. You can add the needed code below it for 2 and 1 character numbers (basically just modify what I wrote and change the SUBSTRINGs accordingly).

Essentially it converts the number to a string, checks how many numbers there are, then breaks it down accordingly.

DECLARE @n int = 515

DECLARE @v varchar(100) = CAST(@N as varchar)

SELECT CASE WHEN LEN(@v) = 3 THEN 
    (CASE   WHEN LEFT(@v, 1) = 1 THEN 'One Hundred'
            WHEN LEFT(@v, 1) = 2 THEN 'Two Hundred'
            WHEN LEFT(@v, 1) = 3 THEN 'Three Hundred'
            WHEN LEFT(@v, 1) = 4 THEN 'Four Hundred'
            WHEN LEFT(@v, 1) = 5 THEN 'Five Hundred'
            WHEN LEFT(@v, 1) = 6 THEN 'Six Hundred'
            WHEN LEFT(@v, 1) = 7 THEN 'Seven Hundred'
            WHEN LEFT(@v, 1) = 8 THEN 'Eight Hundred'
            WHEN LEFT(@v, 1) = 9 THEN 'Nine Hundred'
            ELSE '' END)
    + ' ' +
    (CASE   WHEN SUBSTRING(@v, 2, 1) = 2 THEN 'Twenty'
            WHEN SUBSTRING(@v, 2, 1) = 3 THEN 'Thirty'
            WHEN SUBSTRING(@v, 2, 1) = 4 THEN 'Forty'
            WHEN SUBSTRING(@v, 2, 1) = 5 THEN 'Fifty'
            WHEN SUBSTRING(@v, 2, 1) = 6 THEN 'Sixty'
            WHEN SUBSTRING(@v, 2, 1) = 7 THEN 'Seventy'
            WHEN SUBSTRING(@v, 2, 1) = 8 THEN 'Eighty'
            WHEN SUBSTRING(@v, 2, 1) = 9 THEN 'Ninety'
            WHEN SUBSTRING(@v, 2, 1) = 1 THEN 
                CASE    WHEN SUBSTRING(@v, 2, 2) = 10 THEN 'Ten'
                        WHEN SUBSTRING(@v, 2, 2) = 11 THEN 'Eleven'
                        WHEN SUBSTRING(@v, 2, 2) = 12 THEN 'Twelve'
                        WHEN SUBSTRING(@v, 2, 2) = 13 THEN 'Thirteen'
                        WHEN SUBSTRING(@v, 2, 2) = 14 THEN 'Fourteen'
                        WHEN SUBSTRING(@v, 2, 2) = 15 THEN 'Fifteen'
                        WHEN SUBSTRING(@v, 2, 2) = 16 THEN 'Sixteen'
                        WHEN SUBSTRING(@v, 2, 2) = 17 THEN 'Seventeen'
                        WHEN SUBSTRING(@v, 2, 2) = 18 THEN 'Eighteen'
                        WHEN SUBSTRING(@v, 2, 2) = 19 THEN 'Nineteen'
                        ELSE '' END




            ELSE '' END)
    + ' ' +
    (CASE   WHEN SUBSTRING(@v, 2, 1) = 1 THEN ''
            WHEN SUBSTRING(@v, 3, 1) = 2 THEN 'Two'
            WHEN SUBSTRING(@v, 3, 1) = 3 THEN 'Three'
            WHEN SUBSTRING(@v, 3, 1) = 4 THEN 'Four'
            WHEN SUBSTRING(@v, 3, 1) = 5 THEN 'Five'
            WHEN SUBSTRING(@v, 3, 1) = 6 THEN 'Six'
            WHEN SUBSTRING(@v, 3, 1) = 7 THEN 'Seven'
            WHEN SUBSTRING(@v, 3, 1) = 8 THEN 'Eight'
            WHEN SUBSTRING(@v, 3, 1) = 9 THEN 'Nine'
            WHEN SUBSTRING(@v, 3, 1) = 1 THEN 'One'
            ELSE '' END)
    END