There are a lot of answers out there on how to do this and some are very long and lengthy, some not very efficient and some pretty ingenious ones. I have altered these to make a few variations, first being NumToWords and ,second, DaysToWords.
NumToWords will take a number and return the word equivalent; ie: 16 to Sixteen (16) - case sensitivity is a preference and easily altered.
DaysToWords is similiar however the words are day specific not number word. Using the same example would result in Sixteenth (16th). Below is the coding for each one. Giving credit to the original creator Srinivas Sampath, with my own alterations and tweaks.
Numbers to Words
CREATE FUNCTION [dbo].[udf_NumToWords] (
@Number Numeric (38, 0) -- Input number with as many as 18 digits
) RETURNS VARCHAR(8000)
/*
* Converts a integer number as large as 34 digits into the
* equivalent words.
*
* Attribution: Based on NumberToWords by Srinivas Sampath
* as revised by Nick Barclay and Mike Christiansen
*
* Example:
if number is 61: RETURNS: "sixty-one (61)"
If number is 1001: RETURNS: "one thousand one (1,001)
*
****************************************************************/
AS BEGIN
DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)
IF @Number = 0 Return 'zero (0)'
-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
, @outputString = ''
, @counter = 1
SELECT @length = LEN(@inputNumber)
, @position = LEN(@inputNumber) - 2
, @loops = LEN(@inputNumber)/3
-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1
-- insert data for the numbers and words
INSERT INTO @NumbersTable SELECT '00', ''
UNION ALL SELECT '01', 'one' UNION ALL SELECT '02', 'two'
UNION ALL SELECT '03', 'three' UNION ALL SELECT '04', 'four'
UNION ALL SELECT '05', 'five' UNION ALL SELECT '06', 'six'
UNION ALL SELECT '07', 'seven' UNION ALL SELECT '08', 'eight'
UNION ALL SELECT '09', 'nine' UNION ALL SELECT '10', 'ten'
UNION ALL SELECT '11', 'eleven' UNION ALL SELECT '12', 'twelve'
UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'
UNION ALL SELECT '15', 'fifteen' UNION ALL SELECT '16', 'sixteen'
UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'
UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'
UNION ALL SELECT '30', 'thirty' UNION ALL SELECT '40', 'forty'
UNION ALL SELECT '50', 'fifty' UNION ALL SELECT '60', 'sixty'
UNION ALL SELECT '70', 'seventy' UNION ALL SELECT '80', 'eighty'
UNION ALL SELECT '90', 'ninety'
WHILE @counter <= @loops BEGIN
-- get chunks of 3 numbers at a time, padded with leading zeros
SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)
IF @chunk <> '000' BEGIN
SELECT @tensones = SUBSTRING(@chunk, 2, 2)
, @hundreds = SUBSTRING(@chunk, 1, 1)
, @tens = SUBSTRING(@chunk, 2, 1)
, @ones = SUBSTRING(@chunk, 3, 1)
-- If twenty or less, use the word directly from @NumbersTable
IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
SET @outputString = (SELECT word
FROM @NumbersTable
WHERE @tensones = number)
+ CASE @counter WHEN 1 THEN '' -- No name
WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '
WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '
WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '
WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '
ELSE '' END
+ @outputString
END
ELSE BEGIN -- break down the ones and the tens separately
SET @outputString = ' '
+ (SELECT word
FROM @NumbersTable
WHERE @tens + '0' = number)
+ '-'
+ (SELECT word
FROM @NumbersTable
WHERE '0'+ @ones = number)
+ CASE @counter WHEN 1 THEN '' -- No name
WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '
WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '
WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '
WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '
ELSE '' END
+ @outputString
END
-- now get the hundreds
IF @hundreds <> '0' BEGIN
SET @outputString = (SELECT word
FROM @NumbersTable
WHERE '0' + @hundreds = number)
+ ' hundred '
+ @outputString
END
END
SELECT @counter = @counter + 1
, @position = @position - 3
END
-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, ' ', ' '))) + ' (' + convert(varchar,FORMAT(@Number,'#,##')) +')'
SET @outputstring = LEFT(@outputstring, 1) + SUBSTRING(@outputstring, 2, 8000)
RETURN @outputString -- return the result
END
Days to Words
There is little change from NumToWords. For one I have limited the numbers to under 100 as I did not see a need for a large number format in most legal documentation beyond 100; generally used when talking about the day of the month, which will not exceed 31. I am toying with the idea of altering this to qualify entry to verify that it is a valid day - ie using a date entry instead of a number. As you read through it you'll see the differences.
CREATE FUNCTION [dbo].[udf_DaysToWords] (
@Number Numeric (5, 0) -- Input number up to 99
) RETURNS VARCHAR(8000)
/*
* Converts a integer number under 100 into the
* equivalent words and number.
*
* Attribution: Based on NumberToWords by Srinivas Sampath
*
*
* Example:
if number is 61: RETURNS: "sixty-first (61st)"
If number is 15: RETURNS: "fifteenth (15th)"
****************************************************************/
AS BEGIN
DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(15))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)
DECLARE @nth varchar(2) = ''
IF @Number > 99 Return 'Invalid: Value must be between 1 and 100'
/* Rerun if number or day is entered as a negative number */
If @Number < 0 Return dbo.udf_DaysToWords (ABS(@Number))
-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
, @outputString = ''
, @counter = 1
SELECT @length = LEN(@inputNumber)
, @position = LEN(@inputNumber) - 2
, @loops = LEN(@inputNumber)/3
-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1
-- insert data for the numbers and words
INSERT INTO @NumbersTable SELECT '00', ''
UNION ALL SELECT '01', 'first' UNION ALL SELECT '02', 'second'
UNION ALL SELECT '03', 'third' UNION ALL SELECT '04', 'fourth'
UNION ALL SELECT '05', 'fifth' UNION ALL SELECT '06', 'sixth'
UNION ALL SELECT '07', 'seventh' UNION ALL SELECT '08', 'eighth'
UNION ALL SELECT '09', 'nineth' UNION ALL SELECT '10', 'tenth'
UNION ALL SELECT '11', 'eleventh' UNION ALL SELECT '12', 'twelveth'
UNION ALL SELECT '13', 'thirteenth' UNION ALL SELECT '14', 'fourteenth'
UNION ALL SELECT '15', 'fifteenth' UNION ALL SELECT '16', 'sixteenth'
UNION ALL SELECT '17', 'seventeenth' UNION ALL SELECT '18', 'eighteenth'
UNION ALL SELECT '19', 'nineteenth' UNION ALL SELECT '20', 'twentieth'
UNION ALL SELECT '30', 'thirtieth' UNION ALL SELECT '40', 'fourtieth'
UNION ALL SELECT '50', 'fiftieth' UNION ALL SELECT '60', 'sixtieth'
UNION ALL SELECT '70', 'seventieth' UNION ALL SELECT '80', 'eightieth'
UNION ALL SELECT '90', 'ninetieth'
WHILE @counter <= @loops BEGIN
-- get chunks of 3 numbers at a time, padded with leading zeros
SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)
IF @chunk <> '000' BEGIN
SELECT @tensones = SUBSTRING(@chunk, 2, 2)
, @tens = SUBSTRING(@chunk, 2, 1)
, @ones = SUBSTRING(@chunk, 3, 1)
-- If twenty or less, use the word directly from @NumbersTable
IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
SET @outputString = (SELECT word
FROM @NumbersTable
WHERE @tensones = number)
+ @outputString
END
ELSE BEGIN -- break down the ones and the tens separately
SET @outputString = ' '
+ (SELECT word
FROM @NumbersTable
WHERE @tens + '0' = number)
+ '-'
+ (SELECT word
FROM @NumbersTable
WHERE '0'+ @ones = number)
+ @outputString
END
END
SELECT @counter = @counter + 1
, @position = @position - 3
END
--Nth declaration
SET @nth = CASE WHEN @Number BETWEEN 11 and 19 THEN 'th' ELSE
CASE RIGHT(@Number,1) WHEN 1 THEN 'st' WHEN 2 THEN 'nd' WHEN 3 THEN 'rd' ELSE 'th' END
END
-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, ' ', ' '))) + ' (' + convert(varchar,@Number) + @nth +')'
SET @outputstring = LEFT(@outputstring, 1) + SUBSTRING(@outputstring, 2, 8000)
RETURN @outputString -- return the result
END
GO
Hope this has been helpful. If so please mark below. Thanks
