Search code examples
sql-servercurrency

How can i use custom separator characters money values?

I want to show result from my price table with specific format like this:

$2536360.23 ==> 2"536"360/23

I´ve wrote this code, Please help me to complete:

Declare     @Input           Money = 2536360.23
,           @Seprator        Char(1) = '"'
,           @DecimalPointer  Char(1) = '/'
Solution

  • I agree with Larnu that this should not really be done in the database layer, but it is possible.

    So if you are just interested in learning the "how":

    Declare     @Input           Money = 2536360.23
,           @Seprator        Char(1) = '"'
,           @DecimalPointer  Char(1) = '/'

SELECT 
    REPLACE(
        REPLACE(
            REPLACE(                
                FORMAT(@Input, 'C', 'en-us'), -- this puts it in the expected starting format $2,536,360.23
                                              -- if you skip the last parameter, it would be based on whatever your local culture is set to
                                              -- e.g. I am British so for me it would say £2,536,360.23
            ',', @Seprator), 
        '.', @DecimalPointer), 
    '$', '');