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:
- World Currency Formats and ISO 4217 Information
- World Currency Symbols [UNICODE Currency Symbols]
- The SQL# Library Developed by Solomon Rutzky [ISO country codes]
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.