Convert varchar2 column to number in Oracle

oracletype conversion

I have a table emp_details.

The column emp_salary is of type varchar2 in which certain rows are null:

emp_salary
100,34
null
20,2
30,3
null

I need a result in which the column type is number:

emp_salary
100.34
null
20.2
30.3
null

My attempt in code is:

select emp_salary 
from emp_details
where emp_salary = to_number(emp_salary ,'9999D99','nls_numeric_characters=,.');

Error:

ORA-01722: invalid number
01722. 00000 – "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

How can I fix it?

I don't mean to compare the values. I just want to change the varchar to number. I will use it in a small part of my bigger function, using TO_NUMBER.

Best Answer

You are sort of almost there. You sound like you're doing a conversion of a selected value rather than a filter and you need to cater for NULLs so:-

SELECT TO_NUMBER(NVL(emp_salary,'0'),'9999D99','nls_numeric_characters=,.')
FROM emp_details

Related Solutions

Changes in Oracle Database implicit conversion rules for select statement

So you have a VARCHAR column, and a NUMBER filter. The implicit conversion indeed allows a NUMBER -> VARCHAR conversion, but the problem is that when applying the filter, Oracle tries to do it the other way, ie. to convert the VARCHAR column into a number, thus the error gets thrown :)

Don't really see any solutions here except updating the SQL query. This is why nobody recommends the usage of implicit conversions. They could also cause a slight decrease of performance.

EDIT: If you don't have access to the query and have to wait for the dev team, but instead you have enough privileges on the database, and know for sure that the VARCHAR column holds only number, you could try to alter the table and convert the column type.

Convert string to number from xml at server side

cast relies on your session's NLS settings to convert between datatypes.

You should use to_number with an explicit number format model, something like:

SELECT to_number(nvl(trim(replace(xmltype(t.sce_msg).extract('//RoofInsulationCSV/Total/text()', 'xmlns="http://INT010.GPA.Schemas.External.GPA_AllFile').getstringval(),'.',',')),0), '999999999,99')
FROM        ta_main.serv_cons_event t
INNER JOIN  ta_main.event e
      ON    t.sce_evt_id_s = e.evt_id_s
WHERE       e.evt_typ_id_c IN ('INV', 'ACC');

You'll have to play around with the format model to fit your expected data.

Best Answer

Related Solutions

Changes in Oracle Database implicit conversion rules for select statement

Convert string to number from xml at server side

Related Question