Oracle: Exclude Results From Query Using a Regular Expression

Question

Using linq (.net 3.5 +) and predicate builder, I did it like so:

var startsWith3Chars = new System.Text.RegularExpressions.Regex(@"^[a-zA-Z]{3}\-", System.Text.RegularExpressions.RegexOptions.Compiled);
wherePredicate = wherePredicate.And(x => startsWith3Chars.Matches(x.MATERIALUID).Count > 0);

But now I have the need to do this filtering within the command text.

Is there a way to use something like REGEXP_INSTR to limit the results based on a regular expression?

Answer 1

How about REGEXP_LIKE?

Answer 2

Given this test data ...

SQL> select name
  2  from t23
  3  /

NAME
----------
SAM-I-AM
MR KNOX
X11
CAT
LORAX

SQL>

... the following query uses REGEXP_LIKE() to return records whose first four characters contain only letters or hyphens:

SQL> select name
  2  from t23
  3  where regexp_like(name, '^[[:alpha:]\-]{4}')
  4  /

NAME
----------
SAM-I-AM
LORAX

SQL>

We can also use REGEXP_INSTR() with the same basic pattern (I have dropped the leading caret):

SQL> select name
  2  from t23
  3  where regexp_instr(name, '[[:alpha:]\-]{4}', 1) = 1
  4  /

NAME
----------
SAM-I-AM
LORAX

SQL>

Oracle added full regular expression support to its SQL in version 10g. Find out more.