Home » SQL & PL/SQL » SQL & PL/SQL » How to retrieve negative value as positive in simple brackets? (11gR2, Windows 10)
How to retrieve negative value as positive in simple brackets? [message #664291] Tue, 11 July 2017 06:59 Go to next message
oracbeg
Messages: 16
Registered: July 2017
Junior Member
Can anybody please help me with this? Is there any function or any way where i can customise the output negative numeric value as positive and in brackets. For eg: the output value should come in form of (123) from -123. i have tried to_char function along with numeric formatting {TO_CHAR(round(-123, '999PR')} it gives output in angular brackets like <123> but i want it as (123) in simple brackets.
Re: How to retrieve negative value as positive in simple brackets? [message #664292 is a reply to message #664291] Tue, 11 July 2017 07:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: How to retrieve negative value as positive in simple brackets? [message #664293 is a reply to message #664292] Tue, 11 July 2017 07:41 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use CASE, abs and ||
Re: How to retrieve negative value as positive in simple brackets? [message #664294 is a reply to message #664291] Tue, 11 July 2017 07:46 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
PR returning angle brackets is monumentally dumb. Here's another way,
orclz> select translate(to_char(-1234.56,'999999.99PR'),'<>','()') from dual;

TRANSLATE(TO_CHAR(-1234.56,'999999.99PR'),'<
--------------------------------------------
  (1234.56)

orclz>
Re: How to retrieve negative value as positive in simple brackets? [message #664295 is a reply to message #664294] Tue, 11 July 2017 07:55 Go to previous messageGo to next message
oracbeg
Messages: 16
Registered: July 2017
Junior Member
It worked. Awesome Smile Thanks a ton for your response. Smile
Re: How to retrieve negative value as positive in simple brackets? [message #664296 is a reply to message #664295] Tue, 11 July 2017 08:06 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
REGEXP solution:

SQL> select regexp_replace(-1234.56,'^-(.+)$','(\1)') from dual; 

REGEXP_RE
---------
(1234.56)

SQL> select regexp_replace(1234.56,'^-(.+)$','(\1)') from dual;

REGEXP_
-------
1234.56

SQL> 

SY.
Previous Topic: Cast_to_number not working
Next Topic: uploading excel with transposed data
Goto Forum:
  


Current Time: Thu Mar 28 09:49:31 CDT 2024