?
Solved

oracle varchar2 to number

Posted on 2013-05-13
22
Medium Priority
?
840 Views
Last Modified: 2013-06-05
I have a character coming in the file 1234567891011.92518-,

I wanted to convert into a number in oracle and move the - sign to the front

where A.xxxx_AMT = 1234567891011.92518-

CASE WHEN SUBSTR(A.xxxx_AMT,-1,1) = '-'
      THEN TO_NUMBER(SUBSTR(A.xxxx_AMT,1,LENGTH(A.xxxx_AMT) -1)) * -1
      ELSE TO_NUMBER(A.xxxx_AMT)
END AS xxxx_AMT


the result I expected was -1234567891011.92518

but I am getting this -1234567891011.93
0
Comment
Question by:aboha
  • 6
  • 6
  • 6
  • +1
20 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39161047
select to_number('1210.73', '999999999999999999.999999') from dual

i.e. use a format mask within the to_number()
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 600 total points
ID: 39161056
select
CASE WHEN SUBSTR('1234567891011.92518-',-1,1) = '-'
      THEN TO_NUMBER(SUBSTR('1234567891011.92518-',1,LENGTH('1234567891011.92518-') -1),'999999999999999999.99999') * -1
      ELSE TO_NUMBER('1234567891011.92518','999999999999999999.99999') 
END AS xxxx_AMT
from dual

Open in new window

0
 

Author Comment

by:aboha
ID: 39161100
The above solution still rounds decimal places to this
but I am getting this -1234567891011.93

when expecting  -1234567891011.92518
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1200 total points
ID: 39161120
SELECT   CASE SUBSTR('1234567891011.92518-', -1) WHEN '-' THEN -1 ELSE 1 END
       * TO_NUMBER(RTRIM('1234567891011.92518-', '-'))
  FROM DUAL;


this assumes the value might or might not have a '-' on the end.


if it will always have a '-'  then you can simplify


SELECT   -1 * TO_NUMBER(RTRIM('1234567891011.92518-', '-'))
  FROM DUAL;
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39161131
did you try the 'from dual' example? did that return more the 2 decimal places?

http://sqlfiddle.com/#!4/70992/1

is that sql snippet from select query?
or are you running an update/insert?
(if update/insert what is the data precision of the field)
0
 

Author Comment

by:aboha
ID: 39161146
even when running from dual I get this problem, I have run exactly the query that you posted still the decimal places are getting rounded off to two decimal places

@PORTLETPAUL
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39161265
not sure why...
How are you executing the queries? (i.e. through what?)

e.g. the sqlfiddle url above you will see more than 2 decimals
(& my sql tools are not reducing the output to 2 decimals)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39161354
several of the options above appear to work just fine
you may be having issues with how large values are displayed though


SQL> set numwidth 20
SQL> select
  2  CASE WHEN SUBSTR('1234567891011.92518-',-1,1) = '-'
  3        THEN TO_NUMBER(SUBSTR('1234567891011.92518-',1,LENGTH('1234567891011.92518-') -1),'999999999999999999.99999')
 * -1
  4        ELSE TO_NUMBER('1234567891011.92518','999999999999999999.99999')
  5  END AS xxxx_AMT
  6  from dual;

            XXXX_AMT
--------------------
-1234567891011.92518

SQL> SELECT   CASE SUBSTR('1234567891011.92518-', -1) WHEN '-' THEN -1 ELSE 1 END
  2         * TO_NUMBER(RTRIM('1234567891011.92518-', '-'))
  3    FROM DUAL;

CASESUBSTR('1234567891011.92518-',-1)WHEN'-'THEN-1ELSE1END*TO_NUMBER(RTRIM('1234
--------------------------------------------------------------------------------
                                                            -1234567891011.92518

SQL> SELECT   -1 * TO_NUMBER(RTRIM('1234567891011.92518-', '-'))
  2    FROM DUAL;

-1*TO_NUMBER(RTRIM('1234567891011.92518-','-'))
-----------------------------------------------
                           -1234567891011.92518

Open in new window

0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1200 total points
ID: 39161372
another option, if the values will always have a '-' on the end

simply use the S or MI mask on TO_NUMBER



SQL> set numwidth 20
SQL> SELECT   TO_NUMBER('1234567891011.92518-','9999999999999.99999S')
  2    FROM DUAL;

TO_NUMBER('1234567891011.92518-','9999999999999.99999S')
--------------------------------------------------------
                                    -1234567891011.92518

SQL> SELECT   TO_NUMBER('1234567891011.92518-','9999999999999.99999MI')
  2    FROM DUAL;

TO_NUMBER('1234567891011.92518-','9999999999999.99999MI')
---------------------------------------------------------
                                     -1234567891011.92518
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39161378
Which tool do you use to test these statements (SQL*PLus, SQL Developer, TOAD, etc.)?  Does that have an environment setting somewhere that limits your number format to just two decimal digits?

Are you inserting the result into a table?  If yes, how is that column defined?  Is that something like "number(20,2)"?  That would force the result to be rounded to two decimal places.
0
 

Author Comment

by:aboha
ID: 39161879
@markgeer  I am using toad, It happens only with this number
other numbers like 22.45678

works fine and displays as it is
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39161943
I'm using toad 11.6.1.6 and I don't get rounding problems using the queries above
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 39162375
Do you view the result of this calculation directly in TOAD, or do you save this value to a table first, then query that to see the result in TOAD?

Can you test this either in SQL*Plus or in SQL Developer or another tool that can execute SQL statements?

I just noticed that the "to_number" conversion that you posted in your question does *NOT* include an explicit format mask (like this example: to_number(A.xxxx_AMT,'9999999999999.99999').  Without an explicit format mask like that, you are allowing (forcing) either the database, or TOAD to decide for you how many decimal digits are significant.  Apparently, one of them is choosing to use two significant digits.
0
 

Author Comment

by:aboha
ID: 39187756
None of solutions that were given above worked, I would like to close the question
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39187790
ok, but I'm left wondering why several options here all worked for us but not for you,
and there is a publicly available url to show you that the required calculation is indeed working to the required level of precision. did you at least look at that url (< 5 seconds of your time)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39187859
>>None of solutions that were given above worked

in what way did they fail?

wrong results?
no results?
error message?
keyboard catches fire?
system hang?


I posted cut-and-paste of my sql*plus output.
Please do the same for what you tried and that will give us information on how to proceed.


If the problem only surfaces in toad, please post your settings for the toad data grid.
Have you tried "run as script"  ? Does it produce the same problems?
0
 

Author Comment

by:aboha
ID: 39205586
close this question please
0
 

Author Comment

by:aboha
ID: 39205619
I've requested that this question be closed as follows:

Accepted answer: 0 points for aboha's comment #a39205586

for the following reason:

My question could not be answered , I am no longer Interested in knowing it
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39205620
Your question was answered with multiple methods.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39206136
>>My question could not be answered
I'm afraid that is quite untrue
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question