Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 811
  • Last Modified:

oracle varchar2 to number

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
aboha
Asked:
aboha
  • 6
  • 6
  • 6
  • +1
4 Solutions
 
PortletPaulCommented:
select to_number('1210.73', '999999999999999999.999999') from dual

i.e. use a format mask within the to_number()
0
 
PortletPaulCommented:
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
 
abohaAuthor Commented:
The above solution still rounds decimal places to this
but I am getting this -1234567891011.93

when expecting  -1234567891011.92518
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sdstuberCommented:
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
 
PortletPaulCommented:
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
 
abohaAuthor Commented:
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
 
PortletPaulCommented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
abohaAuthor Commented:
@markgeer  I am using toad, It happens only with this number
other numbers like 22.45678

works fine and displays as it is
0
 
sdstuberCommented:
I'm using toad 11.6.1.6 and I don't get rounding problems using the queries above
0
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
abohaAuthor Commented:
None of solutions that were given above worked, I would like to close the question
0
 
PortletPaulCommented:
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
 
sdstuberCommented:
>>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
 
abohaAuthor Commented:
close this question please
0
 
abohaAuthor Commented:
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
 
sdstuberCommented:
Your question was answered with multiple methods.
0
 
PortletPaulCommented:
>>My question could not be answered
I'm afraid that is quite untrue
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 6
  • 6
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now