By: Sebastiao Pereira | Updated: 2024-07-16 | Comments (3) | Related: > TSQL
Problem
Sometimes, it is necessary to have numbers spelled out in words, like when writing a sentence ("Two hundred sixty-one victims were hospitalized."). How do you express numbers in words with T-SQL code?
Solution
Let's create a function to do the job. We need to divide numbers in ranges to spell them out. The ranges are numbers between 1 to 19, 20 to 99, 100 to 999, and the same for thousands, millions, billions, etc.
User-Defined Function to Convert Whole Numbers in Words
Here is a function to allow you to take a number and convert it to a word representation for the number.
-- ==================================================
-- Author: Sebastiao Pereira - MSSQLTips.com
-- Create date: 20240517
-- Description: Number in Words
-- ==================================================
CREATE FUNCTION [dbo].[ufnNumberInWords] (@Number bigint)
RETURNS nvarchar(1500)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @Negative nvarchar(10) = '';
DECLARE @Word nvarchar(1500) = '';
IF @Number < 0
BEGIN
SET @Negative = 'Negative ';
SET @Number = -@Number;
END
IF @Number = 0
SET @Word = ' ';
IF @Number BETWEEN 1 AND 19
SET @Word = CASE
WHEN @Number = 1 THEN 'One'
WHEN @Number = 2 THEN 'Two'
WHEN @Number = 3 THEN 'Three'
WHEN @Number = 4 THEN 'Four'
WHEN @Number = 5 THEN 'Five'
WHEN @Number = 6 THEN 'Six'
WHEN @Number = 7 THEN 'Seven'
WHEN @Number = 8 THEN 'Eight'
WHEN @Number = 9 THEN 'Nine'
WHEN @Number = 10 THEN 'Ten'
WHEN @Number = 11 THEN 'Eleven'
WHEN @Number = 12 THEN 'Twelve'
WHEN @Number = 13 THEN 'Thirteen'
WHEN @Number = 14 THEN 'Fourteen'
WHEN @Number = 15 THEN 'Fifteen'
WHEN @Number = 16 THEN 'Sixteen'
WHEN @Number = 17 THEN 'Seventeen'
WHEN @Number = 18 THEN 'Eighteen'
WHEN @Number = 19 THEN 'Nineteen'
END;
IF @Number BETWEEN 20 AND 99
SET @Word = CASE
WHEN @Number / 10 = 2 THEN 'Twenty'
WHEN @Number / 10 = 3 THEN 'Thirty'
WHEN @Number / 10 = 4 THEN 'Forty'
WHEN @Number / 10 = 5 THEN 'Fifty'
WHEN @Number / 10 = 6 THEN 'Sixty'
WHEN @Number / 10 = 7 THEN 'Seventy'
WHEN @Number / 10 = 8 THEN 'Eighty'
WHEN @Number / 10 = 9 THEN 'Ninety'
END +
CASE WHEN @Number % 10 > 0 THEN '-' + [dbo].[ufnNumberInWords] (@Number % 10)
ELSE ''
END;
IF @Number BETWEEN 100 AND 999
SET @Word = [dbo].[ufnNumberInWords] (@Number / 100) + ' Hundred ' + [dbo].[ufnNumberInWords] (@Number % 100);
IF @Number BETWEEN 1000 AND 999999
SET @Word = [dbo].[ufnNumberInWords] (@Number / 1000) + ' Thousand ' + [dbo].[ufnNumberInWords] (@Number % 1000);
IF @Number BETWEEN 1000000 AND 999999999
SET @Word = [dbo].[ufnNumberInWords] (@Number / 1000000) + ' Million ' + [dbo].[ufnNumberInWords] (@Number % 1000000);
IF @Number BETWEEN 1000000000 AND 999999999999
SET @Word = [dbo].[ufnNumberInWords] (@Number / 1000000000) + ' Billion ' + [dbo].[ufnNumberInWords] (@Number % 1000000000);
IF @Number BETWEEN 1000000000000 AND 999999999999999
SET @Word = [dbo].[ufnNumberInWords] (@Number / 1000000000000) + ' Trillion ' + [dbo].[ufnNumberInWords] (@Number % 1000000000000);
SET @Word = TRIM(@Negative + @Word);
RETURN @Word;
END
Example 1
In a table of booked passengers with columns Flight Number, Seat Category, and the number of Passengers, we can summarize it in the following way:
Example 2
In a table to store legal documents with columns LegalItem and LegalText, we can print it as seen below. Note: if one item is inserted, we do not need to change any text, only its order.
User-Defined Function to Express Currency in Words
Now, we will create another user-defined function to express currency in words using the previous function by adding currency units.
-- ==================================================
-- Author: Sebastiao Pereira - MSSQLTips.com
-- Create date: 20240517
-- Description: Currency in Words
-- ==================================================
CREATE FUNCTION [dbo].[ufnCurrencyInWords] (@Money money)
RETURNS nvarchar(1500)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @Word nvarchar(1500) = ''
,@Dollars nvarchar(1500) = ''
,@Cents nvarchar(1500) = ''
,@DollarSufix nvarchar(10) = ' Dollars'
,@DollarCentsSufix nvarchar(10) = ' Cents'
,@DollarWithCents nvarchar(10) = ''
,@Negative nvarchar(10) = '';
IF @Money < 0
BEGIN
SET @Negative = 'Negative ';
SET @Money = -@Money;
END
IF FLOOR(@Money) = 1
SET @DollarSufix = ' Dollar';
IF FLOOR(@Money) > 0
SET @Dollars = [dbo].[ufnNumberInWords] (FLOOR(@Money)) + @DollarSufix;
IF (@Money % 1) * 100 = 1
SET @DollarCentsSufix = ' Cent';
IF @Money % 1 > 0
BEGIN
IF LEN(@Dollars) > 0
SET @DollarWithCents = ' and ';
SET @Cents = @DollarWithCents + [dbo].[ufnNumberInWords] ((@Money % 1) * 100) + @DollarCentsSufix;
END
SET @Word = @Negative + @Dollars + @Cents;
RETURN @Word;
END
GO
Example 1
Currency in words for $125.65.
Example 2
Currency in words for -$0.25.
Next Steps
- Keep in mind that if you are translating to another language, pay attention
to small details like special words for some numbers or the scale name to represent
a single unit or multiple entities, which will require additional programming.
- Example: One hundred in Spanish is expressed as "cien," but any number greater than 100 is expressed as "ciento …" and in Portuguese, "cem" and "cento e …" respectively.
About the author
Sebastiao Pereira has over 38 years of healthcare experience, including software development expertise with databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-07-16
