REGEXP_LIKE search for multiple words ignoring order

Question

I need to search in string, more words in any order.

For example, wh need a regex to match both row, using words "search" AND "text"

search in this text
in this text search

With a classic regexp I use this:

(?=.*\bsearch\b)(?=.*\btext\b).*

But regexp_like not support \b.

I know i can use a select with a multiple condition

select * from table
where string like '%search%'
and string like '%text%'

but if there is a solution with regexp_like I prefer.

Regards, Marco

Answer 1

Create an Oracle Text index on your column:

CREATE INDEX table_name__value__textidx ON table_name(value) INDEXTYPE IS CTXSYS.CONTEXT;

Then you can use:

select *
from   table_name
where  CONTAINS( value, '$search AND text', 1 ) > 0;

Which would search the value column for words with the search stem (i.e. search, searches, searching), as indicated by the $ prefix on the term, and the exact word text.

So, for the sample data:

CREATE TABLE table_name ( value ) AS
SELECT 'search in this text' FROM DUAL UNION ALL
SELECT '"in this text, you should search" said Yoda' FROM DUAL UNION ALL
SELECT 'searching text is hard' FROM DUAL UNION ALL
SELECT 'we do not want to find text research' FROM DUAL

The query would output:

| VALUE                                       |
| :------------------------------------------ |
| search in this text                         |
| "in this text, you should search" said Yoda |
| searching text is hard                      |

db<>fiddle here

Answer 2

You can use | for matching multiple pattern in REGEXP_LIKE as follows:

SQL> -- Sample data
SQL> WITH DATAA (STR) AS
  2  (
  3  SELECT 'search in this text' FROM DUAL UNION ALL -- will pass REGEXP
  4  SELECT 'in this text search' FROM DUAL UNION ALL -- will pass REGEXP
  5  SELECT 'in this text' FROM DUAL UNION ALL -- will not pass REGEXP
  6  SELECT 'in this search' FROM DUAL -- will not pass REGEXP
  7  )
  8  -- your query
  9  SELECT STR
 10    FROM DATAA
 11   WHERE REGEXP_LIKE ( STR, '(search)(.)*(text)|(text)(.)*(search)' );

STR
-------------------
search in this text
in this text search

SQL>