Link to home
Create AccountLog in
Avatar of aboha
aboha

asked on

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
Avatar of PortletPaul
PortletPaul
Flag of Australia image

select to_number('1210.73', '999999999999999999.999999') from dual

i.e. use a format mask within the to_number()
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of aboha
aboha

ASKER

The above solution still rounds decimal places to this
but I am getting this -1234567891011.93

when expecting  -1234567891011.92518
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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)
Avatar of aboha

ASKER

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
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)
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

SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
Avatar of aboha

ASKER

@markgeer  I am using toad, It happens only with this number
other numbers like 22.45678

works fine and displays as it is
I'm using toad 11.6.1.6 and I don't get rounding problems using the queries above
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of aboha

ASKER

None of solutions that were given above worked, I would like to close the question
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)
>>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?
Avatar of aboha

ASKER

close this question please
Avatar of aboha

ASKER

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
Your question was answered with multiple methods.
>>My question could not be answered
I'm afraid that is quite untrue