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?
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.
How about REGEXP_LIKE?
