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:
![Flight Booking](/tipimages2/8019_expressing-numbers-in-words-with-t-sql-code-1.png)
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.
![Legal Document](/tipimages2/8019_expressing-numbers-in-words-with-t-sql-code-2.png)
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.
![Amount To Pay](/tipimages2/8019_expressing-numbers-in-words-with-t-sql-code-3.png)
Example 2
Currency in words for -$0.25.
![Currency in Words for $ -0.25](/tipimages2/8019_expressing-numbers-in-words-with-t-sql-code-4.png)
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
![MSSQLTips author Sebastiao Pereira](/images/Sebastiao-Pereira.png)
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