CODEWITHSUNDEEP
sqlsql-servert-sql
Philip
Philip

Reputation: 2628

Changing formatting of currency

I have the following sample data:

CREATE TABLE [dbo].[Money](
    [MoneyValue] [money] NOT NULL
)
GO

INSERT [dbo].[Money] ([MoneyValue]) VALUES (99.9900)
INSERT [dbo].[Money] ([MoneyValue]) VALUES (-99.9900)
GO

The following query:

select 
CONCAT('$', MoneyValue) 
from 
Money

returns the following results

$99.99
$-99.99

Is there a way I can return the results as the following instead?

$99.99
-$99.99

Upvotes: 2

Views: 73

Answers (4)

sticky bit
sticky bit

Reputation: 37472

As another alternative to the already posted answers, you can use replicate() and sign() to put a '-' in front of it, when the number is negative. Then concat() the '$' and the absolute value (using abs()).

SELECT concat(replicate('-', -sign(moneyvalue)), '$', abs(moneyvalue))
       FROM money;

db<>fiddle

Be aware, that replicate() returns NULL for zero or negative lengths. In a concat() that is no problem, as concat() converts nulls to empty strings. But if you use the + string concatenation operator instead, you'd have to do that conversion yourself (using coalesce() or isnull() etc.).

Upvotes: 0

GarethD
GarethD

Reputation: 69759

You should really leave all formatting to your presentation layer, but if for whatever reason you must do this in SQL you can use FORMAT

SELECT FORMAT([MoneyValue], '$0.00')
FROM dbo.[Money]

but beware of doing this on large sets, it may not perform well.

Upvotes: 4

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Try this 

DECLARE @money MONEY = -123

SELECT 
    REPLACE(CONCAT('$', @money) ,'$-','-$')

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Why not just use replace() :

SELECT REPLACE(CONCAT('$', MoneyValue), '$-', '-$')
FROM Money;

Upvotes: 0

Related Questions