Express a Number or Currency in Words with T-SQL Code

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.

Sebastiao Pereira

Sebastião Pereira has over 40 years of experience in database development including T-SQL, algorithm design, machine learning and bringing innovative mathematical formulas to SQL Server. He started his career at a transnational fast-moving consumer goods (FMCG) company as an employee then later transitioning into a consultant role. He eventually founded his own company to develop software solutions for the healthcare industry.  Sebastião is a respected award-winning author on MSSQLTips.com extending SQL Server capabilities beyond traditional workloads.

• MSSQLTips Awards
  • Author of the Year – 2025
  • Trendsetter (25+ tips) – 2025
  • Rookie of the Year – 2024