0

I have a varchar column in oracle DB that stores numeric and character values.

ID : VALUE
01 : 12
02 : 15
03 : 16
04 : 08
05 : Positive

I got error when I used numeric condition in where clause.

WHERE VALUE > 10

How can i fix this situation?

Nawaf
  • 540
  • 1
  • 5
  • 14

2 Answers2

1

If you are on Oracle 12cR2 then you can use default value when conversion error happens.

SQL>
SQL> CREATE TABLE YOUR_TABLE AS
  2  (SELECT '01' AS ID , '12' AS VALUE FROM DUAL UNION ALL
  3  SELECT '02', '15' FROM DUAL UNION ALL
  4  SELECT '03', '16' FROM DUAL UNION ALL
  5  SELECT '04', '08' FROM DUAL UNION ALL
  6  SELECT '05', 'Positive' FROM DUAL);

Table created.

SQL>
SQL> SELECT * FROM YOUR_TABLE
  2  where TO_NUMBER(value DEFAULT -1 ON CONVERSION ERROR) > 10;

ID VALUE
-- --------
01 12
02 15
03 16

Cheers!!

Popeye
  • 35,427
  • 4
  • 10
  • 31
0

As you stated, your value column consists of character values.

To apply correct numeric condition on it, you have to turn it into number:

SELECT *
  FROM table
 WHERE 10 < CASE value 
              WHEN 'Positive' THEN null 
              WHEN 'Negative' THEN null 
              ELSE TO_NUMBER(value) 
            END
Goran Kutlaca
  • 2,014
  • 1
  • 12
  • 18
  • It works if i have only three possible entries ( numeric , positive and negative). – Nawaf Oct 01 '19 at 09:13
  • @Nawaf yes, that's what I concluded from example in question. Do you have more types of value? If yes, with `TO_NUMBER()` you can only convert strings made of numeric characters. There is no way to precisely compare strings with non numeric characters either than converting them manually (aka using `CASE`) or ignoring them (similar to what Tejash posted) – Goran Kutlaca Oct 01 '19 at 09:23