Cool Tips‎ > ‎Data Conversion‎ > ‎

Data Conversion - Spell out Numbers

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Anthony Zackin
Date:        22 Jan 2009

Description

This must have been the product of a very rainy weekend.  I've yet to find a use for it, but undoubtedly i will someday.
Anyway, this code does a reasonably effective job of converting Integer and Decimal notation data to (US) English text.
 
It would be a relatively trivial task to convert to (UK) English, or any other language.
 
The code is fairly extensively commented, so I won't repeat it here.
 

Code

Code for fnSpellInteger function:

SET NOCOUNT ON

-- =============================================
-- Author: Copyright 2009 Anthony Zackin
-- Create date: 01-22-09
-- Description: Converts an integer into words, viz.,
-- master.dbo.fnSpellInteger(10129) ==> "Ten Thousand One Hundred Twenty-Nine"
-- =============================================
USE MASTER

IF
OBJECT_ID('dbo.fnSpellInteger') IS NOT NULL DROP FUNCTION fnSpellInteger
GO

CREATE FUNCTION dbo.fnSpellInteger ( @number int )
RETURNS VARCHAR(100)
AS
BEGIN
  
-- For debugging outside of the UDF: DECLARE @debug bit  SET @debug = 0

  
DECLARE @result VARCHAR(100), @word VARCHAR(100), @group VARCHAR(100)
  
DECLARE @i int, @j int, @m int, @digit VARCHAR(2), @cn VARCHAR(20)

  
IF @number = 0 RETURN 'Zero'

  
SELECT @result = '', @word = '', @group = ''
  
  
SET @cn = @number
  
SET @cn = REPLACE(@cn,',','')
  
SET @m = LEN(@cn) % 3
  
IF @m > 0 SET @cn = REPLICATE('0',3-@m) + @cn                -- Left pad with zeroes to a multiple of 3

  
SET @i = 1
  
SET @j = LEN(@cn)-@i+1
  
SET @m = @i % 3
  
WHILE @i <= LEN(@cn)
  
BEGIN
      
-- @i is 1 origin index into numeric string while @m = @i modulo 3
       -- If the middle digit of each group of 3 is a '1' then this is a 'Ten' or a '...teen'
      
IF @m = 2 AND SUBSTRING(@cn,@i,1) = '1'
      
BEGIN
           SET
@digit = SUBSTRING(@cn,@i,2)
          
-- Skip rightmost digit of 3 if processing teens
          
SET @i = @i + 1
      
END
       ELSE
           SET
@digit = SUBSTRING(@cn,@i,1)

      
SET @word =
      
CASE
          
WHEN @m = 0 THEN                                    -- Rightmost digit of group of 3
              
CASE @digit
                  
WHEN '0' THEN ''
                  
WHEN '1' THEN 'One'
                  
WHEN '2' THEN 'Two'
                  
WHEN '3' THEN 'Three'
                  
WHEN '4' THEN 'Four'
                  
WHEN '5' THEN 'Five'
                  
WHEN '6' THEN 'Six'
                  
WHEN '7' THEN 'Seven'
                  
WHEN '8' THEN 'Eight'
                  
WHEN '9' THEN 'Nine'
              
END +
              
CASE
                  
WHEN (@group <> '' OR @digit <> '0') AND (@j+2) / 3 = 2 THEN ' Thousand'
                  
WHEN (@group <> '' OR @digit <> '0') AND (@j+2) / 3 = 3 THEN ' Million'
                  
WHEN (@group <> '' OR @digit <> '0') AND (@j+2) / 3 = 4 THEN ' Billion'
                  
ELSE ''
              
END
           WHEN
LEN(@digit) = 2 THEN                           -- Special case when middle digit is a '1'
              
CASE @digit
                  
WHEN '10' THEN 'Ten'
                  
WHEN '11' THEN 'Eleven'
                  
WHEN '12' THEN 'Twelve'
                  
WHEN '13' THEN 'Thirteen'
                  
WHEN '14' THEN 'Fourteen'
                  
WHEN '15' THEN 'Fifteen'
                  
WHEN '16' THEN 'Sixteen'
                  
WHEN '17' THEN 'Seventeen'
                  
WHEN '18' THEN 'Eighteen'
                  
WHEN '19' THEN 'Nineteen'
              
END +
              
CASE
                  
WHEN (@group <> '' OR @digit <> '00') AND (@j+2) / 3 = 2 THEN ' Thousand'
                  
WHEN (@group <> '' OR @digit <> '00') AND (@j+2) / 3 = 3 THEN ' Million'
                  
WHEN (@group <> '' OR @digit <> '00') AND (@j+2) / 3 = 4 THEN ' Billion'
                  
ELSE ''
              
END
           WHEN
@m = 2 THEN                                    -- Middle digit of group of 3
              
CASE @digit
                  
WHEN '2' THEN 'Twenty'
                  
WHEN '3' THEN 'Thirty'
                  
WHEN '4' THEN 'Forty'
                  
WHEN '5' THEN 'Fifty'
                  
WHEN '6' THEN 'Sixty'
                  
WHEN '7' THEN 'Seventy'
                  
WHEN '8' THEN 'Eighty'
                  
WHEN '9' THEN 'Ninety'
                  
ELSE ''
              
END
           WHEN
@m = 1 THEN                                    -- Leftmost digit of group of 3
              
CASE @digit
                  
WHEN '0' THEN ''
                  
WHEN '1' THEN 'One'
                  
WHEN '2' THEN 'Two'
                  
WHEN '3' THEN 'Three'
                  
WHEN '4' THEN 'Four'
                  
WHEN '5' THEN 'Five'
                  
WHEN '6' THEN 'Six'
                  
WHEN '7' THEN 'Seven'
                  
WHEN '8' THEN 'Eight'
                  
WHEN '9' THEN 'Nine'
              
END +
              
CASE WHEN @digit <> '0' THEN ' Hundred' ELSE '' END
       END

       SET
@group = @group + RTRIM(@word)                       -- Group value

      
IF @word <> ''
      
BEGIN
           DECLARE
@prefix VARCHAR(20)
          
IF CHARINDEX(' ',@word) > 0 SET @prefix = LEFT(@word,CHARINDEX(' ',@word)) ELSE SET @prefix = @word
          
IF RIGHT(@result,2) = 'ty' AND @prefix IN ('One','Two','Three','Four','Five','Six','Seven','Eight','Nine')
              
SET @result = @result + '-' + LTRIM(@word)
          
ELSE
               SET
@result = @result + ' ' + LTRIM(@word)
      
END
      
-- Thhe following needs to be outside of a UDF to work:
       --IF @debug = 1 SELECT @cn as 'Number', @i as '@i', @j as '@j', @m as '@m', @digit as '@digit', CAST(replace(@group,' ','`') AS CHAR(30)) as '@group', @word as '@word', @result as '@result'
      
SET @i = @i + 1
      
SET @j = LEN(@cn)-@i+1
      
SET @m = @i % 3
      
IF @m = 1 SET @group = ''                              -- Clear group value when starting a new one

  
END

   IF
@result = '' SET @result = '0'
  
RETURN LTRIM(@Result)

END
GO

 
 

Code for fnSpellNumber function:

 SET NOCOUNT ON

-- =============================================
-- Author: Copyright 2009 Anthony Zackin
-- Create date: 01-22-09
-- Description: Converts a string numeric expression into words, viz.,
-- master.dbo.fnSpellNumber('15.99') ==> "Fifteen and Ninety-Nine Hundredths"
-- Notes: Uses fnSpellInteger to convert an integer into words
-- Example: fnSpellNumber can be used to generate pseudo-random test character data
/*
   set nocount on
   declare @rand int, @i int
   set @rand = rand(131)*100
   set @i = 0

   while @i < 5
   begin
       set @i = @i + 1
       select 'insert(id,number,words) values(' + cast(@i as varchar(5)) + ',' +
           cast(@rand as varchar(5)) + ',''' + master.dbo.fnspellnumber(@rand) + ''')'
       set @rand = rand()*100
   end
*/
-- =============================================
USE MASTER

IF
OBJECT_ID('master.dbo.fnSpellNumber') IS NOT NULL DROP FUNCTION fnSpellNumber
GO

CREATE FUNCTION dbo.fnSpellNumber ( @number varchar(20) )
RETURNS VARCHAR(200)
AS
--For debugging: declare @number varchar(20) set @number = '192.1'
BEGIN
  
-- This is for use outside of a function: DECLARE @debug bit  SET @debug = 0

  
DECLARE @result varchar(200), @word varchar(100)
  
DECLARE @i int, @intpart varchar(20), @decpart varchar(20)
  
  
SET @word = LTRIM(RTRIM(@number))
  
-- Check for a bad number, e.g., one with embedded spaces
  
IF ISNUMERIC(@word) = 0 RETURN '<< NOT A NUMBER >>'

  
SET @i = CHARINDEX('.', @word)
  
-- Remove trailing zeroes for any decimal portion
  
IF @i > 0 -- Number contains a decimal point
  
BEGIN
       WHILE
RIGHT(@word,1) = '0' SET @word = LEFT(@word,LEN(@word)-1)
      
IF @word = '' SET @word = '0'
  
END
  
-- Insert a decimal point at the end if none was specified
  
IF @i = 0 -- No decimal point
  
BEGIN
       SET
@word = @number + '.'
      
SET @i = CHARINDEX('.', @word)
  
END

   SET
@intpart = LEFT(@word,@i-1)    -- Extract the integer part of the number if any
  
IF LEN(@intpart) > 0
      
SET @result = master.dbo.fnSpellInteger(CAST(@intpart AS int))
  
ELSE
       SET
@result = ''

  
-- Extract the decimal portion of the number
  
SET @decpart = RIGHT(@word,LEN(@word)-@i)  -- @i is position of decimal point

  
IF LEN(@decpart) > 0
  
BEGIN
       IF
@result = 'Zero'
          
SET @result = ''
      
ELSE IF @result <> ''
          
SET @result = @result + ' and '

      
SET @result = @result + master.dbo.fnSpellInteger(@decpart) +
          
CASE LEN(@decpart)
              
WHEN 0 THEN ''
              
WHEN 1 THEN ' Tenths'
              
WHEN 2 THEN ' Hundredths'
              
WHEN 3 THEN ' One-Thousandths'
              
WHEN 4 THEN ' Ten-Thousandths'
              
WHEN 5 THEN ' One-Hundred-Thousandths'
              
WHEN 6 THEN ' One-Millionths'
              
WHEN 7 THEN ' Ten-Millionths'
              
WHEN 8 THEN ' One-Hundred-Millionths'
              
WHEN 9 THEN ' One-Billionths'
          
END
      
-- Check for a valid plural
      
IF @decpart = 1 SET @result = LEFT(@result, LEN(@result)-1) -- Remove last "s" for just 1
  
END
  
  
-- This is for use outside of a function: if @debug = 1 select @word as '@word', @i as '@i', @intpart as '@intpart', @decpart as '@decpart', @result as '@result'
  
  
RETURN @result

END
GO


 Test Code:

SELECT 0,master.DBO.fnSpellInteger(0)
SELECT 9,master.DBO.fnSpellInteger(9)
SELECT 19,master.DBO.fnSpellInteger(19)
SELECT 29,master.DBO.fnSpellInteger(29)
SELECT 129,master.DBO.fnSpellInteger(129)
SELECT 1929,master.DBO.fnSpellInteger(1929)
SELECT 10029,master.DBO.fnSpellInteger(10029)
SELECT 10129,master.DBO.fnSpellInteger(10129)
SELECT 192129,master.DBO.fnSpellInteger(192129)
SELECT 1092129,master.DBO.fnSpellInteger(1092129)
SELECT 1092.129,master.DBO.fnSpellInteger(1092.129) -- Use fnSpellNumber for this


SELECT ' ', master.dbo.fnSpellNumber(' ')
SELECT 'a', master.dbo.fnSpellNumber('a')
SELECT '1a', master.dbo.fnSpellNumber('1a')
SELECT 'a1', master.dbo.fnSpellNumber('a1')
SELECT '1. ', master.dbo.fnSpellNumber('1. ')
SELECT '1..', master.dbo.fnSpellNumber('1..')
SELECT '1 .', master.dbo.fnSpellNumber('1 .')
SELECT '. 1', master.dbo.fnSpellNumber('. 1')
SELECT '1. 1', master.dbo.fnSpellNumber('1. 1')
SELECT '1.1.1', master.dbo.fnSpellNumber('1.1.1')
SELECT '.1A', master.dbo.fnSpellNumber('.1A')
SELECT '0.A1', master.dbo.fnSpellNumber('0.A1')
SELECT '0.1', master.dbo.fnSpellNumber('0.1')
SELECT '0.9', master.dbo.fnSpellNumber('0.9')
SELECT '0 ', master.dbo.fnSpellNumber('0 ')
SELECT ' 9 ', master.dbo.fnSpellNumber(' 9 ')


SELECT '92093', master.dbo.fnSpellNumber('92093')
SELECT '920093', master.dbo.fnSpellNumber('920093')
SELECT '15.002', master.dbo.fnSpellNumber('15.002')
SELECT '15.99', master.dbo.fnSpellNumber('15.99')
SELECT '.121', master.dbo.fnSpellNumber('.121')
SELECT '.120', master.dbo.fnSpellNumber('.120')
SELECT '15.9901', master.dbo.fnSpellNumber('15.9901')
SELECT '15.99001', master.dbo.fnSpellNumber('15.99001')
SELECT '15.990001', master.dbo.fnSpellNumber('15.990001')
SELECT '15.9900001', master.dbo.fnSpellNumber('15.9900001')
SELECT '99.99000001', master.dbo.fnSpellNumber('99.99000001')
SELECT '9900001.0', master.dbo.fnSpellNumber('9900001.0')
SELECT '99000001.99000001', master.dbo.fnSpellNumber('99000001.99000001')
SELECT '00110.2000', master.dbo.fnSpellNumber('00110.2000')
SELECT '00110', master.dbo.fnSpellNumber('00110')



ċ
Spell out Numbers.sql
(9k)
Andy Hughes,
Jun 23, 2012, 1:22 AM
Comments