Home » SQL & PL/SQL » SQL & PL/SQL » inbuilt oracle function
inbuilt oracle function [message #189268] Wed, 23 August 2006 22:52 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
is there any function in oracle to change the value from
56158.34-
70181.62-
484651.36
136580.19
11796.69-

to
-56158.34
-70181.62
484651.36
136580.19
-11796.69
Re: inbuilt oracle function [message #189275 is a reply to message #189268] Thu, 24 August 2006 00:03 Go to previous messageGo to next message
sanjit
Messages: 65
Registered: November 2001
Member
Here is the function for same, can anyone tell how to get the negative value

FUNCTION convert_picamt(p_string_in IN VARCHAR2)
  RETURN NUMBER IS
    --
    lv_sign_txt            VARCHAR2(1);
    lv_string_txt          VARCHAR2(15);
    lv_new_string_txt      VARCHAR2(15);
    lv_new_number_num      NUMBER;
    lv_last_position_num   NUMBER;
   
  BEGIN
     IF TRIM(p_string_in) IS NULL
    THEN
      RETURN 0;
    END IF;
       lv_string_txt        := p_string_in;
    lv_sign_txt          := SUBSTR(lv_string_txt,-1,1);
    lv_last_position_num := LENGTH (lv_string_txt);
   IF lv_sign_txt = '-' THEN
     lv_new_string_txt := SUBSTR(lv_string_txt,1,lv_last_position_num-1    ELSE
      lv_new_string_txt := lv_string_txt;
    END IF;
      lv_new_number_num := TO_NUMBER(lv_new_string_txt);
      RETURN (lv_new_number_num);
  
  EXCEPTION
  WHEN OTHERS THEN
    RAISE;
  END convert_picamt;
Re: inbuilt oracle function [message #189289 is a reply to message #189275] Thu, 24 August 2006 00:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
SQL> select to_number('12345.65-', 'fm9999999999D99MI') from dual;

TO_NUMBER('12345.65-','FM9999999999D99MI')
------------------------------------------
                                 -12345.65

SQL> select to_number('12345.65', 'fm9999999999D99MI') from dual;

TO_NUMBER('12345.65','FM9999999999D99MI')
-----------------------------------------
                                 12345.65
Re: inbuilt oracle function [message #189290 is a reply to message #189289] Thu, 24 August 2006 00:44 Go to previous message
sanjit
Messages: 65
Registered: November 2001
Member
Frank,

Why this is not returning ??

select to_number(TO_CHAR('12345.65-', '9999999999S'), 'fm9999999999D99MI') from dual;

I have a char value in this field therfore I need to pass as return to number.

any input would be helpful.

[Updated on: Thu, 24 August 2006 01:02]

Report message to a moderator

Previous Topic: Getting only the first returned row from a select query
Next Topic: Mutation Error
Goto Forum:
  


Current Time: Sun Dec 04 19:10:03 CST 2016

Total time taken to generate the page: 0.07725 seconds