1

I am using the below query to get Ids like 12766% and 39998%. How I can use regexp_like to check 12%66 and 39%98?

select * from table t where regexp_like( UPPER(ID),'12766|39998')
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
ai03
  • 61
  • 2
  • 10
  • 1
    I am really confused on what you want to match. Sample data that matches and doesn't match would really help. For instance is `'%'` meant to be a percentage sign or a `LIKE` wildcard? – Gordon Linoff Feb 25 '21 at 12:09
  • The above query can be used for multiple patterns with wildcard search. But, with this query I can only get 1276600,39998000 etc. But, in some cases I want to perform wildcard search in middle of a string to get ids like 1200066,39000098 etc. and I need to pass these strings in one go but not in a loop and I can use only regexp_like function for this. – ai03 Feb 26 '21 at 06:43

2 Answers2

1

You may use the following regex pattern:

^(12[0-9]*66|39[0-9]*98)$

Oracle query:

SELECT *
FROM yourTable
WHERE REGEXP_LIKE(ID, '^(12[0-9]*66|39[0-9]*98)$');

Demo

But actually, you might just want to use LIKE here:

SELECT *
FROM yourTable
WHERE ID LIKE '12%66' OR ID LIKE '39%98';

This would work find, so long as you don't mind exactly what comes in between the digits.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • To use LIKE seems simpler, but it is quite inefficient from the performance perspective. I'm just hitting some limitation set by Oracle people: ```ORA-07455: estimated execution time (60619 secs), exceeds limit (10800 secs) 07455. 00000 - "estimated execution time (%s secs), exceeds limit (%s secs)" *Cause: User attempted to execute an operation whose estimated execution time exceeds the limit specified for the consumer group. *Action: Execute job on behalf of another group, or increase limit.``` – DelphyM Jun 02 '22 at 02:48
0

I found solution for this. We can use the below query for a%d to match strings like abcd,acd,aabcd etc. A period character (.) is a perfect replacement for % in regexp which can support one or more occurrence of any characters supported in database.

      select * from table where REGEXP_LIKE (UPPER (ID), '^12.66.|EY39.98.')
ai03
  • 61
  • 2
  • 10
  • In regular expressions, the dot only matches a single character. To match one or more like you mention, you'd want `.+`. The dot matches a single character, the `+` is a modifier, meaning one or more of the previous character. I suspect you've found that out by now. – Gary_W Mar 03 '21 at 15:04