CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Sanjeev Ajay
Sanjeev Ajay

Reputation: 5

How to use between in regex function

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

Upvotes: 0

Views: 93

Answers (3)

Utsav
Utsav

Reputation: 8093

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

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191275

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

Upvotes: 2

MohanaPriyan
MohanaPriyan

Reputation: 218

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

Upvotes: 0

Related Questions