Wednesday 10 October 2012

how to translate numbers into English words using Sql Server.

Execute the following Microsoft SQL Server T-SQL database example scripts in Query Editor to demonstrate how to translate numbers into English words. 

Recursive UDF (user-defined function) is used to carry out the sql number to English words translation.
 
-- SQL number to words - SQL number to English - convert numbers into words
-- Translate number to text - Translate number to English - SQL number to check printing

USE AdventureWorks2008;
GO
-- SQL user-defined function - UDF - SQL scalar-valued function - SQL number to text
-- SQL numeric to words - integer to English - sql convert number to string

CREATE FUNCTION fnNumberToWords(@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 @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.fnNumberToWords( @Number % 10)
   WHEN @Number BETWEEN 100 AND 999  
     THEN  (dbo.fnNumberToWords( @Number / 100))+' Hundred '+
         dbo.fnNumberToWords( @Number % 100)
   WHEN @Number BETWEEN 1000 AND 999999  
     THEN  (dbo.fnNumberToWords( @Number / 1000))+' Thousand '+
         dbo.fnNumberToWords( @Number % 1000) 
   WHEN @Number BETWEEN 1000000 AND 999999999  
     THEN  (dbo.fnNumberToWords( @Number / 1000000))+' Million '+
         dbo.fnNumberToWords( @Number % 1000000)
   WHEN @Number BETWEEN 1000000000 AND 999999999999  
     THEN  (dbo.fnNumberToWords( @Number / 1000000000))+' Billion '+
         dbo.fnNumberToWords( @Number % 1000000000)
   WHEN @Number BETWEEN 1000000000000 AND 999999999999999  
     THEN  (dbo.fnNumberToWords( @Number / 1000000000000))+' Trillion '+
         dbo.fnNumberToWords( @Number % 1000000000000)
  WHEN @Number BETWEEN 1000000000000000 AND 999999999999999999  
     THEN  (dbo.fnNumberToWords( @Number / 1000000000000000))+' Quadrillion '+
         dbo.fnNumberToWords( @Number % 1000000000000000)
  WHEN @Number BETWEEN 1000000000000000000 AND 999999999999999999999  
     THEN  (dbo.fnNumberToWords( @Number / 1000000000000000000))+' Quintillion '+
         dbo.fnNumberToWords( @Number % 1000000000000000000)
        ELSE ' INVALID INPUT' END
)

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

-- Test number to English translation

SELECT NumberInEnglish=dbo.fnNumberToWords ( 18)
SELECT NumberInEnglish=dbo.fnNumberToWords ( 67)
SELECT NumberInEnglish=dbo.fnNumberToWords ( 947)
-- Nine Hundred Forty-Seven

SELECT NumberInEnglish=dbo.fnNumberToWords ( 984261)
-- Nine Hundred Eighty-Four Thousand Two Hundred Sixty-One

SELECT NumberInEnglish=dbo.fnNumberToWords ( 777999888)
/* Seven Hundred Seventy-Seven Million Nine Hundred Ninety-Nine Thousand
   Eight Hundred Eighty-Eight */

SELECT NumberInEnglish=dbo.fnNumberToWords ( 222777999888)
SELECT NumberInEnglish=dbo.fnNumberToWords ( 555222777999888)
SELECT NumberInEnglish=dbo.fnNumberToWords ( 7446744073709551616)
/*

Seven Quintillion Four Hundred Forty-Six Quadrillion Seven Hundred
Forty-Four Trillion Seventy-Three Billion Seven Hundred Nine Million
Five Hundred Fifty-One Thousand Six Hundred Sixteen
*/
GO

No comments :