How to use between in regex function

Question

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

Can there be more than 3 digits?

Gordon Linoff
– Gordon Linoff

2017-04-10 10:40:32 +00:00
Commented Apr 10, 2017 at 10:40 — Gordon Linoff
– Gordon Linoff, Commented Apr 10, 2017 at 10:40

Alex Poole · Accepted Answer · 2017-04-10 10:41:26Z

2

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

edited Apr 10, 2017 at 10:41

answered Apr 10, 2017 at 10:36

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MT0 · Accepted Answer · 2017-04-10 10:35:18Z

0

CREATE FUNCTION is_number (p_string IN VARCHAR2)
   RETURN INT
IS
   v_new_num NUMBER;
BEGIN
   v_new_num := TO_NUMBER(p_string);
   RETURN 1;
EXCEPTION
WHEN VALUE_ERROR THEN
   RETURN 0;
END is_number;

then use this in query

select X from where is_number(X)=1 and TO_Number(X) between 100 and 150

edited Apr 10, 2017 at 10:35

MT0

173k12 gold badges70 silver badges136 bronze badges

answered Apr 10, 2017 at 10:31

MohanaPriyan

2183 silver badges9 bronze badges

4 Comments

MT0 Over a year ago

Why not return the parsed number from the function or NULL on an error?

MohanaPriyan Over a year ago

function is used to check if value is number or not if it is numeric then returns 1 else it will return 0

Alex Poole Over a year ago

There's no guarantee your function call will be evaluated first here either, so this is still likely to get ORA-01722. With MTO's suggestion it would be OK though.

MT0 Over a year ago

I know what it does but you could simplify it to:

CREATE FUNCTION check_number (p_string IN VARCHAR2) RETURN NUMBER IS BEGIN RETURN TO_NUMBER(p_string); EXCEPTION WHEN VALUE_ERROR  THEN RETURN NULL; END check_number;

and just use that in the BETWEEN filter. As @AlexPoole noted you will get an ORA-01722 if the TO_NUMBER check is performed before the IS_NUMBER check.

Utsav · Accepted Answer · 2017-04-10 10:54:23Z

0

Try this.

with tbl(X) as
(select 'a1b2c3' from dual union all select
'abc' from dual union all select
'123' from dual union all select
'156' from dual )
select * from (
   select * from tbl where
     REGEXP_LIKE(X, '^[[:digit:]]+$') 
               ) t
where x between 100 and 150

edited Apr 10, 2017 at 10:54

answered Apr 10, 2017 at 10:35

Utsav

8,1632 gold badges22 silver badges40 bronze badges

4 Comments

Alex Poole Over a year ago

This will also get ORA-01722 if the (implicit) to_number is evaluated before the regexp_like(). Try it with a table, not a CTE.

Utsav Over a year ago

Oh. let me try.

Utsav Over a year ago

You are right! CTE behavior is more casual it seems. Editing my answer back to previous using subquery. Anyways it is moot now because of your answer.

Alex Poole Over a year ago

Actually I get the same with your original query too; I guess the predicate is being pushed into the subquery, unhelpfully. Depends what mood the optimiser is in I suppose *8-)

Collectives™ on Stack Overflow

How to use between in regex function

3 Answers 3

Comments

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related