Solved

oracle varchar2 to number

Posted on 2013-05-13
22
727 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
22 Comments
 
LVL 48

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 48

Accepted Solution

by:
PortletPaul earned 150 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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 300 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 48

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 48

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 73

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 73

Assisted Solution

by:sdstuber
sdstuber earned 300 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 34

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 73

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 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 50 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 48

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 73

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 73

Expert Comment

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

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now