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
```