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

By:   |   Updated: 2024-07-16   |   Comments (1)   |   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.

Sebastiao Pereira has over 38 years of healthcare experience, including software development expertise with databases.