How to use the regex function

advertisements

I have a field (column in Oracle) called X that has values like "a1b2c3", "abc", "1ab", "123", "156"

I wrote a sql query that returns only the X that holds pure numerical values with no letters; from the example above would be 123 and 156.

Query written using Oracle syntax:

select X from where REGEXP_LIKE(X, '^[[:digit:]]+$')

Result:

123, 156

Additionally I need to write a query to get the value between 100 and 150.

How could I write such a query in Oracle? Something like below:

select X from where REGEXP_LIKE(X, '^[[:digit:]]+$') between 100 and 150


regexp_like() is a condition, so you can't compare it with anything. You can use the regexp_substr() function instead:

where regexp_substr(x, '^[[:digit:]]+$') between 100 and 150

The value returned by the function is either null or a string of digits that can be (implicitly) converted to a number for comparison.

Demo with your sample data:

with your_table (x) as (
  select 'a1b2c3' from dual
  union all select 'abc' from dual
  union all select '1ab' from dual
  union all select '123' from dual
  union all select '156' from dual
)
select x from your_table
where regexp_substr(x, '^[[:digit:]]+$') between 100 and 150;

X
------
123