-1

I have a column which stored numeric and string values. I want to filter those numeric values from that column. Suppose I need to get rows which less than 100.

mytable

id     |    value
-------------------
1          aa
2          103
3          cc
4          90
5          88

suppose above in above table , 'value' column type is varchar.

I want to select value<100 rows.

my result set should be like as follows,

id     |    value
-------------------
4          90
5          88

Here is my imaginery query, But it doesn't

select * from mytable where TO_NUMBER(value)<100;

My question is deferenct than TO_NUMBER("*") case. Important thing is i want do a mathamatical comarison, which is i want to select <100 numbers.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Nwn
  • 561
  • 2
  • 9
  • 33
  • it should be like try parse . If that text value is a number. then it should check the numbers which is less than 100 – Nwn May 04 '18 at 11:54
  • You can always [remove non-numeric chars](https://stackoverflow.com/questions/3968178/oracle-replacing-non-numeric-chars-in-a-string), being careful to keep signs or decimal points when needed, but I guess it'll never perform nicely. – Álvaro González May 04 '18 at 12:01

1 Answers1

3

If the values are decimals, then you can filter out incorrect values by:

 where case
        when regexp_like(value, '^\d+$') and to_number(value) < 100 then 1
        else 0
       end = 1

SQL Fiddle Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Krzysztof Kaszkowiak
  • 886
  • 6
  • 17