Home » SQL & PL/SQL » SQL & PL/SQL » need help in format
need help in format [message #189501] Thu, 24 August 2006 22:30 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
I am trying to convert this
56158.34-
70181.62-
484651.36

to
-56158.34
-70181.62
484651.36
my data type of the above is varchar2 thus didnot work this function
can anyone suggest why


select to_number(TO_CHAR('12345.65-', '9999999999S'), 'fm9999999999D99MI') from dual;
Re: need help in format [message #189503 is a reply to message #189501] Thu, 24 August 2006 22:44 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
HTH!


CTS@ctstestnew:SQL>select case
  2     when instr('45.56-', '-') > 0 then to_number('-'||replace('45.46-','-'))

  3         end case
  4  from dual
  5  /

      CASE
----------
    -45.46

Re: need help in format [message #189507 is a reply to message #189503] Thu, 24 August 2006 23:19 Go to previous messageGo to next message
sanjit
Messages: 65
Registered: November 2001
Member
Thanks dude

I am passing the column but In return this is only return the -ve value data, and rest null.

I have tried to get.

suppose i have data for respective column
like
45.32-
45.32
32.1-

then the command return like
-45.32

-32.1
only

Could you please suggest how to display 45.32 in the return by case statement.

Re: need help in format [message #189512 is a reply to message #189507] Fri, 25 August 2006 00:04 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select * from test_num;

NUM
--------------------
56158.34-
70181.62-
484651.36

SQL> select decode (instr(num,'-'),0,num,('-'||replace(num,'-'))) "Value" from test_num;

Value
---------------------
-56158.34
-70181.62
484651.36
Re: need help in format [message #189525 is a reply to message #189512] Fri, 25 August 2006 00:39 Go to previous messageGo to next message
sanjit
Messages: 65
Registered: November 2001
Member
thanks Ebrian
This was adjectly i was looking for.

Re: need help in format [message #189584 is a reply to message #189501] Fri, 25 August 2006 03:08 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem you are having is that the trailing MI in the format model requires there to be either a +,- or space at the right hand end of the string.

If you can get your strings into that format, you can do this:
Wrote file afiedt.buf

  1  select col_1,to_char(to_number(col_1,'99999999.99MI'),'9999999.99')
  2  from (
  3  select '56158.34-' col_1 from dual union all
  4  select '70181.62-' col_1 from dual union all
  5* select '484651.36 ' col_1 from dual)
SQL> /

COL_1     |TO_CHAR(TO_
----------|-----------
56158.34- |  -56158.34
70181.62- |  -70181.62
484651.36 |  484651.36
Previous Topic: problem with connect by prior
Next Topic: db link
Goto Forum:
  


Current Time: Sun Dec 04 06:18:57 CST 2016

Total time taken to generate the page: 0.23435 seconds