Oracle: exclude results from query using regular expression

Question

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

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 need to do this filtering in the command text.

Is there a way to use something like REGEXP_INSTR to restrict regex-based results?

Answer 1

Given the 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 reset the host carriage):

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

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

SQL>

Oracle SQL 10g. .

Answer 2

REGEXP_LIKE?