SQL – How to format decimal to space delimited currency $ 1 234 567.89

by

The following SQL statement shows how to take a decimal value, e.g. 1234567.89 and convert it to a space delimited currency – that is, instead of using commas to separate values ($ 1,234,567.89) a space is used ($ 1 234 567.89).

The statement uses the FORMAT() function which is only available from SQL Server 2012 onwards.

The statement works by first formatting the decimal as a string using FORMAT, then removing an unnecessary spaces to the LEFT of the string using LTRIM.

SELECT * , '$ ' + LTRIM ( FORMAT( Price, '### ### ###.00' ) ) AS PRICE_FORMATTED
FROM T_Products