Click here to monitor SSC
  • Av rating:
  • Total votes: 23
  • Total comments: 5
Dwain Camps

A SQL-Based Universal Currency Formatter

07 February 2013

SQL Server isn't usually the best place to format dates or currency as strings. It can be a complex task to conform correctly with national and cultural conventions. Just occasionally, though, you need to do it. This is easy in SQL Server 2012, but if you aren't using that, what do you do?

When an application displays results for its users, the results should be easy on the eyes to make sure that they are interpreted properly. Programming languages have therefore developed extensive libraries that are designed specifically for the job of formatting results to make them less ambiguous for the users and clearer to read. Until SQL Server 2012 introduced the FORMAT() function that makes use of .NET’s full language and cultural formatting library, TSQL’s facilities to do this existed but were incomplete because conventional wisdom has it that the task of formatting values as strings should be handled in the application’s presentation tier. Usually, yes, but not inevitably. Occasionally the SQL developer has the task of rendering a value, currency for example, as a string according to the language and culture of the user of the system.

For anyone who has to write for previous versions of SQL Server, the task of formatting currencies for display can be nightmarishly complex. This is because of the wide variety of formats around the world. So, as is the tradition here at Simple Talk, we’ll take the complex and make it simple.

Currency Formatting at its Simplest

Let’s take a look at a simple example of a first attempt at formatting a numeric value for display as a currency.

DECLARE @BigMoney DECIMAL(15,2) = 1000000000  -- one billion

SELECT Amount=@BigMoney
    ,STUFF(STUFF(STUFF(@BigMoney, 8, 0, ','), 5, 0, ','), 2, 0, ',')
-- Results:   1000000000.00   1,000,000,000.00

We have inserted the 3 commas needed to format our big money number in a currency display format. If this were US dollars, all we need to do is concatenate the currency symbol ($) in front of the string.

What can we learn from this? First, it takes three nested STUFFs to insert all the commas we need. Each offset amount (8, 5 and 2) is applied from right to left (inner STUFF to outer STUFF) to ensure changing string lengths don’t affect the offset. Also, each offset is different by exactly 3, which also happens to be the number of digits grouped by the commas.

Unfortunately for us, our simple example doesn’t work too well when we change our number to 10 billion. In that case, the last group will contain four zeroes. But this can be simply fixed as follows:

DECLARE @BigMoney DECIMAL(15,2) = 10000000000  -- ten billion

SELECT Amount=@BigMoney
    ,STUFF(
        STUFF(
            STUFF(@BigMoney, LEN(@BigMoney) - 5, 0, ',')
        ,LEN(@BigMoney) - 8, 0, ',')
    ,LEN(@BigMoney) - 11, 0, ',')
-- Results:   10000000000.00   10,000,000,000.00

Now we’re using the length of our amount, less an offset, to calculate exact placement of the comma. Once again, the offsets themselves are different by exactly 3.

Yet again though, we are stymied because the moment we get to one trillion (or worse yet to a number less than one billion) our simple example won’t work. The solution, of course, is to apply as many STUFFs as there must be commas in our formatted number.

At this point in time, most people would drop back and punt, thinking that the only way to repeatedly apply STUFF to a string is in a loop. But maybe there’s another way. Let’s try this:

DECLARE @BigMoney DECIMAL(15,2) = 1000000000000  -- one trillion

DECLARE @FormattedMoney VARCHAR(100) = @BigMoney

SELECT @FormattedMoney=STUFF(@FormattedMoney, LEN(@FormattedMoney) - n, 0, ',')
FROM (
    SELECT TOP 4 n 
    FROM (VALUES (5),(8),(11),(14)) a(n) 
    ORDER BY n DESC) a

SELECT @BigMoney, @FormattedMoney
-- Results: 1000000000000.00	1,000,000,000,000.00

Notice how we have forced the ordering from our VALUES clause to control processing STUFF from right to left.

Since the largest number of digits SQL can handle in a number is DECIMAL(38,2), we know this means that exactly 36 digits can appear to the left of the decimal and hence up to 12 offsets are required. We just need to make sure we don’t calculate a negative offset, and handle negative numbers, so the above code becomes:

-- A really big number
DECLARE @BigMoney DECIMAL(38,2) = 123456789012345678901234567890123456.33  

DECLARE @FormattedMoney VARCHAR(100) = @BigMoney

SELECT @FormattedMoney=STUFF(@FormattedMoney, LEN(@FormattedMoney) - n, 0, ',')
FROM (
    SELECT TOP 12 n
    FROM (VALUES (5),(8),(11),(14),(17),(20),(23),(26),(29),(32),(35),(38)) a(n)
    ORDER BY n DESC) a
WHERE LEN(@FormattedMoney) - n > 2 + CHARINDEX(@FormattedMoney, '-')

SELECT @BigMoney, @FormattedMoney
-- Results:    123456789012345678901234567890123456.33
-- Formatted:  123,456,789,012,345,678,901,234,567,890,123,456.33

-- A really small number
SELECT @BigMoney     = -123456789012345678901234567890123456.33
    ,@FormattedMoney = -123456789012345678901234567890123456.33

SELECT @FormattedMoney=STUFF(@FormattedMoney, LEN(@FormattedMoney) - n, 0, ',')
FROM (
    SELECT TOP 12 n
    FROM (VALUES (5),(8),(11),(14),(17),(20),(23),(26),(29),(32),(35),(38)) a(n)
    ORDER BY n DESC) a
WHERE LEN(@FormattedMoney) - n > 2 + CHARINDEX(@FormattedMoney, '-')

SELECT @BigMoney, @FormattedMoney
-- Results:    -123456789012345678901234567890123456.33
-- Formatted:  -123,456,789,012,345,678,901,234,567,890,123,456.33

Now we have a pretty good way to insert our commas, the next thing we need to consider is formatting variances across countries.

A Countries Table Containing Currency Formats

Actually we’ll be creating two tables to store our formatting information and countries with their currency. The DDL for these tables is:

IF OBJECT_ID('CurrencyFormats', 'U') IS NOT NULL
DROP TABLE dbo.CurrencyFormats
GO
CREATE TABLE dbo.CurrencyFormats
    (CurrencyFormat     VARCHAR(20) NOT NULL
    ,Offset             INT         NOT NULL
    ,PRIMARY KEY        CLUSTERED   (CurrencyFormat, Offset))

GO
IF OBJECT_ID('Countries', 'U') IS NOT NULL
DROP TABLE dbo.Countries
GO
CREATE TABLE dbo.Countries
    (CountryCode2Letter CHAR(2)         NOT NULL PRIMARY KEY
    ,CountryName        VARCHAR(150)    NOT NULL 
    ,CurrencyCode       CHAR(3)         NULL
    ,CurrencySymbol     NVARCHAR(4)     NULL
    ,CurrencyDecimals   INT             NOT NULL
    ,CurrencyFormat     VARCHAR(20)     NULL
    ,SeparatorChar      VARCHAR(2)      NOT NULL
    ,DecimalChar        CHAR(1)         NOT NULL)

GO

You can run this DDL from the setup script in the resources section of this article. The data has been pulled together from many sources and, while not comprehensive is a pretty good representation of most major countries and currencies. The author takes no responsibility for the accuracy of this data so you should verify the data that is there for any currencies of interest to you. Some of the resources utilized to put this data together include:

Now we’ll present a modification of our prior formatting script that takes advantage of the new Countries and CurrencyFormats tables we just created.

DECLARE @BigMoney DECIMAL(38,3) = -12345678901234567890123456789012345.33  

DECLARE @FormattedMoney VARCHAR(100) = @BigMoney
DECLARE @LMoney INT = LEN(@FormattedMoney) - CHARINDEX('-', @FormattedMoney)
    ,@Count INT = 0

SELECT @FormattedMoney=STUFF(@FormattedMoney, LEN(@FormattedMoney) - Offset, 0, SeparatorChar)
    ,@Count = @Count + 1
FROM dbo.Countries a
CROSS APPLY (SELECT LeadingDigits=PATINDEX('%[^#]%', CurrencyFormat)-1) b
CROSS APPLY (SELECT RecordCount=ISNULL((@LMoney-(8-LeadingDigits))/LeadingDigits, 0)) c
CROSS APPLY (
    SELECT TOP (RecordCount) CurrencyFormat, Offset
    FROM dbo.CurrencyFormats b
    WHERE a.CurrencyFormat = b.CurrencyFormat
    ) d 
WHERE a.CountryCode2Letter = 'US' 

SELECT @BigMoney, @FormattedMoney, LEN(@FormattedMoney), @Lmoney, @Count

The important modifications that we have made are described below:

  • The type of our @BigMoney has been changed to have 3 decimal digits. If you check the data put into the Countries table, you will find that no country uses more than 3 decimal digits.
  • We created the additional local variables: @LMoney and @Count. @LMoney is the initial length of our currency string and is used to calculate a record count (used in the CROSS APPLY). @Count (which we’ll discard later) is useful in testing this script to see the number of STUFFs applied to the currency string.
  • The outer SELECT is from our Countries tables and we limit that to the country of interest in the WHERE clause (e.g., US).
  • The STUFF now inserts the SeparatorCharacter column of the Countries table, which in at least one case is more than one character in length, instead of a comma.
  • We perform two cascading CROSS APPLYs to calculate the number of leading digits from our CurrencyFormat, and use that to calculate the record count we’ll need to retrieve from our CurrencyFormats table to process the full length of the string. The challenge was to get this just right and must be precise otherwise you’ll find separator characters inserted in awkward places in some currency amounts.
  • It was necessary to discard the table row constructor (VALUES) syntax in favor of retrieving the offsets from an existing table to ensure row ordering, otherwise inconsistent results appeared.
  • The final CROSS APPLY actually selects the offsets we’ll apply in the STUFF to get to our final, formatted currency string. It is important to note the CLUSTERED INDEX on this table, which is designed to return our offsets from smallest to largest, ensuring that our STUFFs are applied right to left in our currency string.

We recommend you familiarize yourself with this script, by testing various values for @BigMoney for these countries: US, JP, SZ and IN, all of which have unique aspects to their currency formats. This way you can assure yourself that the algorithm is sound.

IN (India) is particularly intriguing because no other country formats their currency in quite the same manner:

##,##,##,###.##

Most countries have a format similar to the US: ###,###,###.## with some variation in the decorator characters (the symbol grouping thousands and the decimal point). SZ (Swaziland) is similar to the US except that it inserts a space after the comma. JP (Japan) does not use any grouping symbol (that we know of) and the standard decimal point, so you’ll find that its CurrencyFormat specification in the Countries table is NULL.

The final result for each case will retain the 3 decimal digits, but not to worry as we’ll be taking care of that later.

A SQL FUNCTION for Universal Currency Formatting

Now that we’ve identified a basic algorithm for our formatting, we shall construct a Table Valued Function (TVF) that utilizes this along with some final formatting of the result to include things like the currency symbol and the appropriate decimal point symbol.

CREATE FUNCTION [dbo].[UniversalCurrencyFormatter] 
-- Author: D. Camps
-- Date: 04-Feb-2013
-- Remarks: UniversalCurrencyFormatter is designed to apply international formatting
--          standards to a decimal amount including decorators (e.g., comma separators
--          in the correct positions and decimal symbol), along with the currency code
--          or symbol depending on which is selected.
(
    -- The amount to be formatted
    @BigMoney               DECIMAL(38,3)
    -- The 2 character ISO country code
    ,@CountryCode           CHAR(2)
    -- How to format the currency symbols - allowed values are:
    ,@Currency              INT  -- 0=none (default), 1=symbol, 2=code
)
RETURNS @Results            TABLE (FormattedCurrency NVARCHAR(100))
WITH SCHEMABINDING
AS 
BEGIN 

    DECLARE @FormattedMoney NVARCHAR(100) = @BigMoney
        ,@CurrencyDecimals  INT
        ,@CurrencySymbol    NVARCHAR(3)
        ,@CurrencyCode      CHAR(3)
        ,@DecimalChar       CHAR(1);

    DECLARE @LMoney         INT = LEN(@FormattedMoney) - CHARINDEX('-', @FormattedMoney);

    -- STUFF the separator character into the formatted currency as often as needed
    -- The CASE is for special handling when RecordCount=0 (no formatting required)
    SELECT @FormattedMoney  = 
        CASE RecordCount 
            WHEN 0 THEN @FormattedMoney
            ELSE STUFF(@FormattedMoney
                ,LEN(@FormattedMoney) - Offset
                ,0
                ,a.SeparatorChar) END
        -- These 4 pieces of information are retained for final formatting of the output string
        ,@CurrencyDecimals  = a.CurrencyDecimals    -- Number of decimal digits
        ,@CurrencyCode      = a.CurrencyCode        -- Three character ISO currency code
        ,@CurrencySymbol    = a.CurrencySymbol      -- The UNICODE currency symbol
        ,@DecimalChar       = a.DecimalChar         -- The decimal (decorator) character
    FROM dbo.Countries a
    -- Calculate the number of records to use from the CurrencyFormats table
    CROSS APPLY (SELECT LeadingDigits=PATINDEX('%[^#]%', CurrencyFormat)-1) b
    CROSS APPLY (SELECT RecordCount=ISNULL((@LMoney-(8-LeadingDigits))/LeadingDigits, 0)) c
    -- Our CurrencyFormats table provides the offsets from the right for each format type
    OUTER APPLY (   -- Use OUTER APPLY in case RecordCount = 0
        -- Rows are retrieved in increasing offset sequence due to CLUSTERED INDEX
        SELECT TOP (RecordCount) CurrencyFormat, Offset
        FROM dbo.CurrencyFormats b
        WHERE a.CurrencyFormat = b.CurrencyFormat
        ) d 
    WHERE a.CountryCode2Letter = @CountryCode
    OPTION(MAXDOP 1);

    INSERT INTO @Results
    -- Decide what leading currency symbol to include 
    SELECT CASE WHEN @Currency IS NULL THEN N''
            WHEN @Currency=1 THEN
                -- Use the UNICODE currency symbol 
                CASE WHEN @CurrencySymbol IS NULL THEN N'' 
                    ELSE @CurrencySymbol END
            -- Use the currency code with a trailing blank space
            WHEN @Currency=2 THEN @CurrencyCode + N' ' 
            ELSE N'' END +
        -- Include the formatted whole currency amount
        LEFT(@FormattedMoney, LEN(@FormattedMoney) - 4) + 
        -- And the correct decimal decorator if required
        CASE @CurrencyDecimals WHEN 0 THEN N'' ELSE @DecimalChar END +
        -- Strip off any unneeded decimal digits from our formatted currency string
        LEFT(RIGHT(@FormattedMoney, 3), @CurrencyDecimals); 

    RETURN;

END

Note that we have changed the type of our OutputResult to NVARCHAR because our currency symbols are mostly UNICODE characters (up to 4). We also included the OPTION(MAXDOP 1) to avoid SQL parallelizing the query, which although unlikely could have a negative impact on the results, as the rows must be processed in a serial fashion.

Finally, here’s a small test harness that you can use to proof the results.

;WITH Countries (CountryCode2Letter) AS (
        SELECT 'US' UNION ALL SELECT 'GB' UNION ALL SELECT 'AR' UNION ALL SELECT 'JP'
        UNION ALL SELECT 'PL' UNION ALL SELECT 'IN' UNION ALL SELECT 'SZ'
        UNION ALL SELECT 'BH' UNION ALL SELECT 'AU'
        ),
    BigMoney (Amount) AS (
        SELECT 12345678901234567890123456789012345.333
        UNION ALL SELECT -12345678901234567890123456789012345.333),
    OtherParms (Currency) AS (
        SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2)
SELECT CountryCode2Letter, Currency, FormattedCurrency
FROM Countries
CROSS JOIN BigMoney
CROSS JOIN OtherParms
CROSS APPLY dbo.UniversalCurrencyFormatter(Amount,CountryCode2Letter,Currency)
ORDER BY CountryCode2Letter, Currency, Amount

Selected results returned by the test harness with a description of what you’ll see:

CountryCode2Letter   Currency    FormattedCurrency
-- Argentine Pesos formatted with the currency code (large negative amount)
AR                   2           ARS -12.345.678.901.234.567.890.123.456.789.012.345,33
-- Australian Dollars formatted with the currency code (large positive amount)
AU                   2           AUD 12 345 678 901 234 567 890 123 456 789 012 345.33
-- Indian Rupees formatted with the ISO standard currency symbol
IN                   1           र-12,34,56,78,90,12,34,56,78,90,12,34,56,78,90,12,345.33
-- Japaneses Yen with no separator characters or decimal digits
JP                   0           12345678901234567890123456789012345
JP                   1           ¥-12345678901234567890123456789012345
-- Polish Zlotys using blank as the separator character and comma for the decimal point
PL                   1           zł-12 345 678 901 234 567 890 123 456 789 012 345,33
-- Swaziland with two separator characters (currency symbol is unknown)
SZ                   1           12, 345, 678, 901, 234, 567, 890, 123, 456, 789, 012, 345.33
-- US Dollars formatted as we’d expect
US                   1           $12,345,678,901,234,567,890,123,456,789,012,345.33

A timing test of this TVF found that about 100,000 numbers can be formatted in about 12.5 seconds on a Core i5 (2.5 GHz) laptop.

We explored several other options for this approach and found this one to be a reasonable compromise between speed and flexibility.

We sincerely hope that this example and test data is a useful addition to your SQL tool chest.

Dwain Camps

Author profile:

Dwain Camps has been a project manager for many years. Because performance of applications can be a critical success factor for projects, he has been evangelizing on the need to develop highly performing SQL. By mentoring and authoring articles on SQL, he hopes to train a future generation of software engineers on the right and wrong ways to deliver SQL code. He also has a special interest in developing solutions to complex, data intensive problems using high performance SQL because the declarative nature of SQL allows development of algorithmically unique solutions that procedural languages may not be capable of.

Search for other articles by Dwain Camps

Rate this article:   Avg rating: from a total of 23 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Well written
Posted by: Anonymous (not signed in)
Posted on: Monday, February 18, 2013 at 4:26 AM
Message: Just today we were looking at this very issue! Thank you.

Subject: Nice
Posted by: HolgerSchmeling (view profile)
Posted on: Tuesday, February 19, 2013 at 12:38 AM
Message: We're just discussing, if we gonna use this approach. Thanks!
For the sake of performance, may I suggest re-writig your function as an Inline TVF? This can certainly be done by introducing CTEs, thus avoiding the need for temporary local variables. In case, we'll adopt the solution, I'll certailny do it on my own and publish the modified version as a further comment.

Regards,
Holger

Subject: Using FORMAT() prior to SQL 2012
Posted by: srutzky (view profile)
Posted on: Tuesday, February 19, 2013 at 2:35 PM
Message: Hi Dwain. Great article and thanks for the mention of SQL# :).

I had added the FORMAT() function to SQL# last May (the Date_Format() function) but it is only for the DATE datatype. After reading this article I added 3 more variations of it for Integer, Float, and Decimal datatypes. FormatInteger handles BIGINT, INT, SMALLINT, and TINYINT; FormatFloat handles FLOAT and REAL; and FormatDecimal handles DECIMAL/NUMERIC, MONEY, and SMALLMONEY.

For shops that are using SQL 2005 and/or 2008 and allow SQLCLR, this should make it even easier to handle culture differences as well as custom format strings using #, etc notation :).

These new functions will be included in the next release, which should be within the next two weeks.

Take care,
Solomon...

Subject: To Holger
Posted by: Dwain.C (view profile)
Posted on: Monday, March 04, 2013 at 11:52 PM
Message: Thanks for dropping the comment. Rewriting the approach as an iTVF may be a bit challenging because of the need to hold onto some temporary variables that are DECLAREd right after the BEGIN. Nonetheless, I would have tried to had I been able at the time.

And sorry for belated reply to your comments. I expected to receive a notification but perhaps I missed it.

Subject: To Solomon
Posted by: Dwain.C (view profile)
Posted on: Monday, March 04, 2013 at 11:54 PM
Message: You're welcome for the reference to SQL#. I like the library even though I confess I haven't had the opportunity to fully explore it. It would be pretty cool if you had a version that could access the C# cultural libraries, which probably provide a lot of functionality beyond currency formatting.

I'm wondering why I didn't get a notification that you'd made an update to the library (thought I'd signed up for that).

I also want to highly commend you on the installation process, which is very, very clean straightforward and fast.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.