Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle varchar2 to number

Posted on 2013-05-13
22
Medium Priority
?
797 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 6
  • +1
22 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

704 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