CODEWITHSUNDEEP
sqloracle-databaseplsqlregexp-like
ai03
ai03

Reputation: 81

How to use regexp_like for wildcard search in oracle?

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')

Upvotes: 1

Views: 2588

Answers (2)

ai03
ai03

Reputation: 81

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.')

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

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.

Upvotes: 1

Related Questions