How to convert VARCHAR2 to FLOAT in select statement in Oracle SQL-CodePudding

select mudel 
from mudel 
where 50 > TO_NUMBER(voimsus)

ERROR: ORA-01722: invalid number

Voimsus is a float number ex. 50.21 but datatype is VARCHAR2. Any idea how to convert it. Also I cant change datatype because there is data and there are child records.

Data

Datatypes

SOLUTION: I changed every comma to decimal with

UPDATE mudel 
SET voimsus = REPLACE(voimsus, ',', '.') 
WHERE voimsus LIKE '%,%'

and it worked

CodePudding user response：

Assuming that your session's nls_numeric_characters setting specifies that a period is your decimal separator and a comma is your grouping separator (i.e. it has a value of ".,")

select * 
  from nls_session_parameters 
 where parameter = 'NLS_NUMERIC_CHARACTERS'

while the data in your table uses a comma as the decimal separator, that would be the problem. You could specify the NLS parameters you want to use in your to_number call

create table my_table( incorrect_data_type varchar2(10) );

insert into my_table( incorrect_data_type ) values( '123,45');

select to_number( incorrect_data_type, 
                  '9999D99', 
                  q'{nls_numeric_characters=',.'}') 
  from my_table;

Or you could change your session's settings

alter session set nls_numeric_characters = ',.'

select to_number( incorrect_data_type) from my_table;

Of course, if you solve the problem by changing your session's settings, that means that when you (or someone else) starts a new session, they'll need to change their session's settings as well (assuming they use the same NLS settings to establish the connection that you are).

The right answer, of course, is to use the correct data type for the column in the first place. Storing numeric data in a character column is going to cause plenty of grief-- this is just one of the ways that tends to go wrong. Fixing the data type now will save you lots of grief down the line.

Here's a fiddle showing the options working (and failing).

CodePudding user response：

When converting strings to numbers, then don't rely on session settings by merely using

to_number(voimsus)

but specify the format stored in the string. For this you must tell the DBMS what the decimal separator is with NLS_NUMERIC_CHARACTERS. E.g.:

TO_NUMBER(voimsus, '9999999999D.99', 'NLS_NUMERIC_CHARACTERS='',.''')

But well, it is of course much better not to store numbers in string columns in the first place. Use a proper number type such as NUMBER(12,2) instead. (And I recommend not to use any float data type, such as (BINARY_FLOAT), because then your stored numbers are not exact, but approximate, e.g. 1.3 may be stored as something like 1.3000001).