Introduction
Sometimes, an application needs to display an amount in words. There are a lot of examples of how to do this in several programming or script languages but none to do this within a database using pure SQL. This example uses German language but may be adapted easily to any other language.
Background
The problem while converting an amount to words is that the spoken expression doesn't simply concatenate the words for each digit but fills some extra words into it - also some spoken digits differ depending on where they are located. For example, in German the digit 7 is spoken Sieben but for 17 is not Siebenzehn but Siebzehn and 117 will be spoken as Einhundertsiebzehn and not EinsZehnSieben or EinsSiebenzehn.
Using the Code
This solution uses 3 SQL functions to do all the work.
To convert any number between -9999999999999999.99 and +9999999999999999.99, simply call:
SELECT [dbo].[Amount2Words] (-123456.07)
// (returns 'MinusEinhundertdreiundzwanzigtausendvierhundertsechsundfünfzigKommaNullSieben')
Here's the main function code:
CREATE FUNCTION [dbo].[Amount2Words]
(
@Amount numeric(18,2)
)
RETURNS nvarchar(255)
AS
BEGIN
DECLARE @bolNegative bit = 0;
DECLARE @strAmount nvarchar(255);
DECLARE @intAmount bigint = 0;
DECLARE @intDecimals int = 0;
DECLARE @intHundreds int = 0;
DECLARE @strEntity nvarchar(10);
DECLARE @intLoop int = 1;
DECLARE @strAmountInWords nvarchar(255) = '';
IF (@Amount IS NOT NULL)
BEGIN
IF (@Amount BETWEEN -9999999999999999.99 AND 9999999999999999.99)
BEGIN
IF (@Amount < 0)
BEGIN
SET @bolNegative = 1;
SET @Amount = (@Amount * -1);
END
SET @intAmount = CAST(@Amount AS bigint);
SET @intDecimals = ((@Amount - @intAmount) * 100);
SET @strAmount = CAST(@intAmount AS nvarchar(255));
WHILE (LEN(@strAmount) > 0)
BEGIN
IF (LEN(@strAmount) > 3)
BEGIN
SET @intHundreds = CAST(RIGHT(@strAmount, 3) AS int);
SET @strAmount = SUBSTRING(@strAmount, 1, (LEN(@strAmount) - 3));
END
ELSE
BEGIN
SET @intHundreds = @strAmount;
SET @strAmount = '';
END
IF (@intLoop = 1)
SET @strAmountInWords = [dbo].[Hundreds2Words] (@intHundreds, '');
IF (@intLoop = 2)
SET @strAmountInWords = ([dbo].[Hundreds2Words] (@intHundreds, _
'tausend') + @strAmountInWords);
IF (@intLoop > 2)
BEGIN
IF (@intHundreds = 1)
BEGIN
IF (@intLoop = 3)
SET @strEntity = 'million';
IF (@intLoop = 4)
SET @strEntity = 'milliarde';
IF (@intLoop = 5)
SET @strEntity = 'billion';
END
ELSE
BEGIN
IF (@intLoop = 3)
SET @strEntity = 'millionen';
IF (@intLoop = 4)
SET @strEntity = 'milliarden';
IF (@intLoop = 5)
SET @strEntity = 'billionen';
END
SET @strAmountInWords = ([dbo].[Hundreds2Words] _
(@intHundreds, @strEntity) + @strAmountInWords);
END
SET @intLoop = (@intLoop + 1);
END
IF (LEN(@strAmountInWords) = 0)
SET @strAmountInWords = 'Null';
SET @strAmountInWords = (@strAmountInWords + 'Komma');
SET @strAmountInWords = (@strAmountInWords + [dbo].[Decimals2Words] _
(@intDecimals));
IF (LEN(@strAmountInWords) > 0)
SET @strAmountInWords = UPPER(SUBSTRING(@strAmountInWords, 1, 1)) _
+ SUBSTRING(@strAmountInWords, 2, 3999);
IF (@bolNegative = 1)
SET @strAmountInWords = 'Minus' + @strAmountInWords;
END
ELSE
SET @strAmountInWords = '(Fehler!)';
END
RETURN @strAmountInWords;
END
As you can see, the function takes the amount parameter as of type numeric(18,2) and will first check if the amount is negative and adds a Minus before the resulting word expression after conversion, if necessary. Then the 2 decimal places are separated because they need a different handling. Within the WHILE loop, the integer part of the amount will be divided into groups of max. 3 digits (called hundreds) beginning from the right end. This enables us to determine the belonging entity (thousands, millions, etc.). The subfunction [Hundreds2Words] will be called with every group of hundreds and takes the entity as a second parameter (Suffix). Finally, the decimal part will be formatted using the subfunction [Decimals2Words] and the result will be added at the end of the resulting expression separated with the key word Komma to signal the following decimal part.
Both subfunctions are working similar by separating the passed value (3 digits for hundreds, 2 digits for decimals) into counters for hundreds, tens and ones in another WHILE loop:
WHILE (@Hundreds > 0)
BEGIN
IF (@Hundreds >= 100)
BEGIN
SET @intHundreds = (@intHundreds + 1);
SET @Hundreds = (@Hundreds - 100);
END
ELSE
BEGIN
IF (@Hundreds >= 10)
BEGIN
SET @intTens = (@intTens + 1);
SET @Hundreds = (@Hundreds - 10);
END
ELSE
BEGIN
SET @intOnes = @Hundreds;
SET @Hundreds = 0;
END
END
END
Note that @Hundreds represents the value passed to the function while the counters start with @int...!
At last, we use some CASE switches to translate the found digit counters to words:
SET @Result = CASE @intHundreds
WHEN 1 THEN 'einhundert'
WHEN 2 THEN 'zweihundert'
WHEN 3 THEN 'dreihundert'
WHEN 4 THEN 'vierhundert'
WHEN 5 THEN 'fünfhundert'
WHEN 6 THEN 'sechshundert'
WHEN 7 THEN 'siebenhundert'
WHEN 8 THEN 'achthundert'
WHEN 9 THEN 'neunhundert'
ELSE ''
END
+ CASE
WHEN (@intOnes = 0) THEN ''
WHEN (@intOnes = 1) AND (@intTens = 0) AND (@Suffix = 'tausend') THEN 'ein'
WHEN (@intOnes = 1) AND (@intTens = 0) AND (Len(@Suffix) > 0) _
AND (@Suffix <> 'tausend') THEN 'eine'
WHEN (@intOnes = 1) AND (@intTens = 0) AND (Len(@Suffix) = 0) THEN 'eins'
WHEN (@intOnes = 1) AND (@intTens = 1) THEN 'elf'
WHEN (@intOnes = 1) AND (@intTens > 1) THEN 'einund'
WHEN (@intOnes = 2) AND (@intTens = 0) THEN 'zwei'
WHEN (@intOnes = 2) AND (@intTens = 1) THEN 'zwölf'
WHEN (@intOnes = 2) AND (@intTens > 1) THEN 'zweiund'
WHEN (@intOnes = 3) AND (@intTens <= 1) THEN 'drei'
WHEN (@intOnes = 3) AND (@intTens > 1) THEN 'dreiund'
WHEN (@intOnes = 4) AND (@intTens <= 1) THEN 'vier'
WHEN (@intOnes = 4) AND (@intTens > 1) THEN 'vierund'
WHEN (@intOnes = 5) AND (@intTens <= 1) THEN 'fünf'
WHEN (@intOnes = 5) AND (@intTens > 1) THEN 'fünfund'
WHEN (@intOnes = 6) AND (@intTens = 0) THEN 'sechs'
WHEN (@intOnes = 6) AND (@intTens = 1) THEN 'sech'
WHEN (@intOnes = 6) AND (@intTens > 1) THEN 'sechsund'
WHEN (@intOnes = 7) AND (@intTens = 0) THEN 'sieben'
WHEN (@intOnes = 7) AND (@intTens = 1) THEN 'sieb'
WHEN (@intOnes = 7) AND (@intTens > 1) THEN 'siebenund'
WHEN (@intOnes = 8) AND (@intTens <= 1) THEN 'acht'
WHEN (@intOnes = 8) AND (@intTens > 1) THEN 'achtund'
WHEN (@intOnes = 9) AND (@intTens <= 1) THEN 'neun'
WHEN (@intOnes = 9) AND (@intTens > 1) THEN 'neunund'
ELSE ''
END
+ CASE
WHEN (@intTens = 1) AND (@intOnes IN (1, 2)) THEN ''
WHEN (@intTens = 1) AND (@intOnes NOT IN (1, 2)) THEN 'zehn'
WHEN (@intTens = 2) THEN 'zwanzig'
WHEN (@intTens = 3) THEN 'dreissig'
WHEN (@intTens = 4) THEN 'vierzig'
WHEN (@intTens = 5) THEN 'fünfzig'
WHEN (@intTens = 6) THEN 'sechzig'
WHEN (@intTens = 7) THEN 'siebzig'
WHEN (@intTens = 8) THEN 'achtzig'
WHEN (@intTens = 9) THEN 'neunzig'
ELSE ''
END;
See the special handling of Ones for values 1 and 2 and in conjunction with value 1 for Tens which need special handling in some cases! The decimals are handled similar but will contain the word Null in place of the Tens if the counter is 0. The part of the Tens is set to the end of the word expression because when spoken, they will be prefixed with the Ones (e.g. Dreizehn instead of ZehnDrei).
You may download the 3 SQL functions from the ZIP archive attached to this tip/trick.
If you need another language, greater amount range or more decimal places, I'm sure you'll find it easy to adapt this example to your needs.
Have fun!
