Download source code - 1.7 KB

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!

Amount to Words