RegEx to match String tokens in any order?

Question

I'm looking for an Oracle Regular Expression that will match tokens in any order.

For example, say I'm looking for "one two".

I would want it to match both, "one token two" "two other one"

The number of tokens might grow larger than two, so generating the permutations for the regex would be a hassel.

Is there an easier way to do this, than this

'(ONE.*TWO)|(TWO.*ONE)'

 i.e

select * 
from some_table t
where regexp_like(t.NAME_KEY, '(ONE.*TWO)|(TWO.*ONE)')

Answer 1

You can use several different regular expressions:

SELECT * 
FROM some_table t
WHERE regexp_like(t.NAME_KEY, 'ONE')
AND regexp_like(t.NAME_KEY, 'TWO')

One issue is that this will also match 'TWONE' which the original regular expression would not match. This can be fixed if you also check for some separating tokens or word boundary.

Also a regular expression is not necessary to match a constant string. You could just use LIKE instead.

Answer 2

Here's an alternative query that uses Full Text Search (FTS) functionality:

WHERE CONTAINS(t.name_key, 'ONE & TWO') > 0

See the Precedence Examples for criteria evaluation explanation.

Related:

• Introduction to Oracle Text