format negative value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

format negative value

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-02-28 : 17:41:59

I have daa something like below:

1234
-1234.66
2345.78
-345.77

I'd like format the negative value - to ().How can I do this? I can use the replace function for -. what about the ) at the end of number?
1234
(1234.66)
2345.78
(345.77)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-28 : 17:54:26

Use ABS function to get its absolute value

SELECT YourColumn = CASE WHEN YourColumn < 0 THEN '(' + ABS(YourColumn) + ')' ELSE YourColumn END
FROM Table1

Tara Kizer
aka tduggan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-01 : 01:29:51

Where do you want to show the formatted data?
If you use Reports format it there to show them in braces if they are negatives. I think all reports will support it.

If you want to do this in SQL query then, slight change on the above code

SELECT YourColumn = CASE WHEN YourColumn < 0 THEN '(' + cast(ABS(YourColumn) as varchar(10))+ ')' ELSE YourColumn END
FROM Table1

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-01 : 01:37:25

you should do it at the front end application as formatting it in SQL Server effectively converting the value to a string and passing string to the front end application. If the front end need further process the data, it will have to convert from string back to numeric again.

----------------------------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources