Query on Regular Expressions in Oracle

Question

Hi, 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 ?

Answer 1

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 ($)"

Answer 2

Unless the regex syntax in Oracle is very different from all other regex implementations then you porbably want to change your [] to (). [est] matches either "e", "s" or "t". (est) on the other hand matches "est".