Search code examples
sqloracle-databaseentity-attribute-value

How to compare various VARCHAR as numbers in Oracle

I have a table with various number formats like:

  • 2
  • 3.44189
  • 4,1
  • -0.0022
  • 9.9E+37
  • 1.9E-12

these are measurements in a varchar2 column and to each measurement there is a specification with upper and lower limit value (in same format), so what i want are measurements in between these borders

SELECT VALUE, LOW, HIGH FROM MEASUREMENTS
WHERE to_number(replace(VALUE,',','.'), '999999999D99999999999999999999999999999999999999','NLS_NUMERIC_CHARACTERS = ''.,''')
    > to_number(replace(LOW,',','.'), '999999999D99999999999999999999999999999999999999','NLS_NUMERIC_CHARACTERS = ''.,''')
AND   to_number(replace(VALUE,',','.'), '999999999D99999999999999999999999999999999999999','NLS_NUMERIC_CHARACTERS = ''.,''') 
    <=to_number(replace(HIGH,',','.'), '999999999D99999999999999999999999999999999999999','NLS_NUMERIC_CHARACTERS = ''.,''')

these query works for every number writing above, except exponential numbers like 9.9E+37. I found a rewriting with TO_CHAR, but it doesnt work with varchar

Have anybody a solution to compare various numbers stored as varchar with each other? thx

Solution

  • If you know they are all numbers, I would suggest using cast():

    select cast(col as float)