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

Solution 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 

Solution 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

Solution 3

Also, you can CREATE TABLE with numbers and their names.

CREATE TABLE tblNumbers (Nmb int, NmbWord varchar(100))
INSERT INTO tblNumbers 
VALUES (1,'first'), (2,'second'),(3,'third'), (4,'forth')

CREATE TABLE SomeTable (nmb int)

INSERT INTO SomeTable 
VALUES (1), (1),(2),(3)

SELECT  N.*
FROM tblNumbers N
     JOIN SomeTable ST ON ST.nmb=N.Nmb

DROP TABLE tblNumbers
DROP TABLE SomeTable

Comments

[JNK]

Then you shouldn't try to do it in SQL

[Danny]

I appreciate that this kind of thing should not be done in the SQL environment but unfortunately we do not have an alternative at the moment.

[JNK]

@Beanz - well in SQL, this is the way to go. There may be an API you can use for this in your application layer. If you are limited to using TSQL then a CASE is about as good as it gets. You could potentially make a TABLE for this and JOIN on that too

[Yuck]

+1 This is really your only choice. You could notice some patterns in English language once you get up to things like twenty-first, thirty-second and so on.

[Danny]

This is certainly a possibility if the need for this increasese. Thanks.

[JNK]

@Beanz - Posting working code for some of this. You can modify as needed.

[JNK]

@Yuck - I extended that in the edit, it actually isn't crazy complicated to account for all numbers under 1000

[Nick Harrison]

This worked well for what I needed. I used it to load a lookup table. It is a nice recursive function that keeps the logic simple.