Home » SQL & PL/SQL » SQL & PL/SQL » suffix or prefix negative value of the column
suffix or prefix negative value of the column [message #185514] Wed, 02 August 2006 01:51 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
Is there any way to prefix or suffix for a column data if that have negative value.

like
-350.88 or 350.88-
Re: suffix or prefix negative value of the column [message #185517 is a reply to message #185514] Wed, 02 August 2006 02:04 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, use "S" in format mask:
SQL> select to_char(-10455.68, 's999G999G990D00') from dual;

TO_CHAR(-10455.
---------------
     -10,455.68
Re: suffix or prefix negative value of the column [message #185520 is a reply to message #185517] Wed, 02 August 2006 02:21 Go to previous messageGo to next message
sanjit
Messages: 65
Registered: November 2001
Member
Thanks its works.

Is there any inbuilt function that can easily put the value with in () if negative otherwise leave it as it is,
like
-480.54 become (480.54)
480.54 become 480.54

I tried Decode but didnot work.

Re: suffix or prefix negative value of the column [message #185522 is a reply to message #185520] Wed, 02 August 2006 02:34 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Not a built-in one I know of, but one is easily created

SQL> CREATE OR REPLACE FUNCTION negative_bracket (v_number IN NUMBER) return VARCHAR2 IS
  2  BEGIN
  3      IF Sign(v_number) = -1 THEN
  4          RETURN '(' || To_Char(Abs(v_number)) || ')';
  5      ELSE
  6          RETURN  To_Char(v_number);
  7      END IF;
  8  END;
  9  /

Function created.


SQL> SELECT negative_bracket(-24) FROM dual;

NEGATIVE_BRACKET(-24)
---------------------
(24)


SQL> SELECT negative_bracket(24) FROM dual;

NEGATIVE_BRACKET(24)
---------------------

24

SQL>
Re: suffix or prefix negative value of the column [message #185524 is a reply to message #185520] Wed, 02 August 2006 02:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
We can do <> as a builtin, if that's any help

SQL> select to_char(-98765.43,'9999999PR') from dual;

TO_CHAR(-
---------
  <98765>
Re: suffix or prefix negative value of the column [message #185558 is a reply to message #185514] Wed, 02 August 2006 05:57 Go to previous message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Using CASE:

SQL> define N = 480.54
SQL> select
  2        case when &N < 0
  3             then '('||abs(&N)||')'
  4             else to_char(&N)
  5        end num
  6  from dual;

NUM
--------
480.54

SQL> define N = -480.54
SQL> /

NUM
--------
(480.54)



Previous Topic: How to update a Long field in Oracle
Next Topic: how to get result into spool file
Goto Forum:
  


Current Time: Sat Dec 10 12:39:29 CST 2016

Total time taken to generate the page: 0.06590 seconds