Query on Regular Expressions in Oracle
oracle, regex
Solution
select 1 from dual where regexp_like('best','^[*est]');
`[]` in regexps means "any of the listed characters"
Inside `[]`, the asterisk loses its special meaning and means just the asterisk.
The regexp above matches any string that begins with `*`, `e`, `s` or `t` (any of the listed characters following the beginning of the string).
To select words ending on `-est`, use this:
select 1 from dual where regexp_like('nest','est$')
which means "the string `est` followed by the end of string (`$`)"
Problem
I'm trying my hand in learning Regular Expressions in Oracle ( rather, my first attempt in doing anything with RegEx). What does the character `^` signify at the start ? The documentation mentions ``` Use the caret and dollar sign to define patterns that match the start or end of a string. ^ defines that start of a string or column 1 of the string. ``` So by using `'^[*est]'` as the pattern, my understanding is that `match anything which has -est as its ending`. However, when I tried it out, ``` SQL> select 1 from dual where regexp_like('test','^[*est]'); 1 ---------- 1 ``` ``` SQL> select 1 from dual where regexp_like('best','^[*est]'); no rows selected ``` ``` SQL> select 1 from dual where regexp_like('fest','^[*est]'); no rows selected ``` Removing the `^` however, and we get ``` SQL> select 1 from dual where regexp_like('fest','[*est]'); 1 ---------- 1 ``` ``` SQL> select 1 from dual where regexp_like('best','[*est]'); 1 ---------- 1 ``` ``` SQL> select 1 from dual where regexp_like('test','^[*est]'); 1 ---------- 1 ``` Why is this so ? Why is it that in the first case, the match happens for `test' but not for others ?