How to custom format a number in T-SQL

Question

If I use this custom mask to format a number in SQL Server, I see that it ignores all but the decimal mask and does it's own thing (which I believe might have something to do with the regional settings of the SQL Server):

DECLARE @Number numeric(20, 5) = 123456789.123;
SELECT @Number, FORMAT( @Number, N'#,##,#0.00' );

Results in: 123,456,789.12 instead of 1,23,45,67,89.12

To FORCE a custom mask, I've been able to figure out that I need to escape the comma and period like this:

DECLARE @Number numeric(20, 5) = 123456789.123;
SELECT @Number, FORMAT( @Number, N'#\,##\,#0.00' )

But that results in: 12345,67,89.12 instead of 1,23,45,67,89.12.

So I need to do a rather elaborate script to first figure out the length of the numbers, then build a complete mask like this:

DECLARE @Number numeric(20, 5) = 123456789.123;
SELECT @Number, FORMAT( @Number, N'##\,##\,##\,##\,#0.00' )

Which then correctly displays: 1,23,45,67,89.12

Simply building a mask like this won't work:

DECLARE @Number numeric(20, 5) = 123456789.123;
SELECT @Number, FORMAT( @Number, N'##\,##\,##\,##\,##\,##\,##\,##\,##\,#0.00' )

Because it results in this: ,,,,,1,23,45,67,89.12

Is there an easier way to do this? The reason we need this is for two reasons:

1. Some countries are 1,234.56 while others are 1.234,56
2. Some countries have seperators in different places like 1,23,456.78

I know that I can apply culture like this:

DECLARE @Number numeric(20, 5) = 123456789.123;
SELECT @Number, FORMAT( @Number, N'#,#0.00', N'en-US' ), FORMAT( @Number, N'#,#0.00', N'hi-IN' )

Which results in 123,456,789.12 and 12,34,56,789.12, but customers want control over the separation characters, like making them spaces, or swopping the comma and period around, etc.

I know that I can roll a complete custom solution, but I wanted to find out whether there was a better approach? I've searched high and low, but couldn't find anything better than a completely custom solution.

Looking forward to your insights.

Answer 1

Thanks for the input everyone, glad to see I wasn't wrong when finding that the solution wasn't trivial. I've come up with the below solution, would appreciate any insights into how it may be improved.

I can see that my solution matches some of your input, so at least I'm happy to see that I was on the right track.

I tried it on a million records and it's performance was more than acceptable. I hear all of you when you say this doesn't belong in the database, but they have valid reasons why they require it there; at the end of the day, the customer gets what the customer wants right? :)

DECLARE @Number numeric(20, 5) = -123456789.9876543;
DECLARE @Group1Digits tinyint = 3;
DECLARE @Group2Digits tinyint = 2;
DECLARE @GroupSymbol nvarchar(2) = N' ';
DECLARE @DecimalSymbol nvarchar(2) = N'-';
DECLARE @DecimalDigits tinyint = 3;

SELECT [Source number] = @Number
    , [Integer part] = ROUND( ABS( @Number ), 0, 1 )
    , [Decimal part] = ABS( @Number ) % 1
    , [Mask] = CONCAT( 
        N'#'
        , @GroupSymbol
        , REPLICATE( N'#', ISNULL( @Group2Digits, @Group1Digits ) )
        , @GroupSymbol
        , REPLICATE( N'#', @Group1Digits - 1 ), N'0'
        , @DecimalSymbol
        , REPLICATE( N'0', @DecimalDigits )
    )

    -- Solution:
    , [Formatted number] = CONCAT( 
        -- 1. Check for a sign and apply it.
        IIF( @Number < 0, N'-', N'' )

        -- 2. Get the integer part as a positive integer.
        , LTRIM( FORMAT( ROUND( ABS( @Number ), 0, 1 ),
            CONCAT( 
                -- 3. Build the secondary part of the mask, supporting cultures like India that use a unique "dual" design like #,##,###
                --  We just replicate by log base 10 (length) of the integer, which is sufficient to achieve an "apprixmation" mask, we'll be trimming off the excess at the end.
                REPLICATE( CONCAT( REPLICATE( N'#', ISNULL( @Group2Digits, @Group1Digits ) ), IIF( @GroupSymbol IN ( N'.', N',' ), CONCAT( N'\', @GroupSymbol ), @GroupSymbol ) ), LOG( ABS( @Number ), 10 ) )
                -- 4. Build the primary part of the mask, e.g. ##0
                , REPLICATE( N'#', @Group1Digits - 1 ), N'0'
            )
        -- 5. Trim off the excess mask.
        ), @GroupSymbol )

        -- 6. Add the decimal symbol.
        , @DecimalSymbol
        -- 7. Format the decimal part of the number.
        , SUBSTRING( FORMAT( ABS( @Number ) % 1, CONCAT( N'.', REPLICATE( N'0', @DecimalDigits ) ), N'en-US' ), 2, @DecimalDigits )
    )
;

Source number	Integer part	Decimal part	Mask	Formatted number
-123456789.98765	123456789.00000	0.98765	# ## ##0-000	-12 34 56 789-988