Why does the REGEXP_LIKE function treat the letter 'e' as upper case instead of lower case?

Question

In my pl/sql script Oracle treats the letter 'e' as upper case when searching by the [:upper:] Character Class Syntax.

i.e.

REGEXP_LIKE('e', '[:upper:]')

REGEXP_LIKE('e', '[:lower:]')

Related Oracle docs can be found here:

Oracle - Multilingual Regular Expression Syntax

Oracle - REGEXP_LIKE

Answer 1

The character classes seem to work when you surround them with brackets [] as in :

SQL> SELECT * FROM dual WHERE regexp_like('e', '[[:upper:]]');

DUMMY
-----

SQL> SELECT * FROM dual WHERE regexp_like('E', '[[:upper:]]');

DUMMY
-----
X

When you use single brackets Oracle treats them as a list of characters, i-e the following works because u is contained in the string :upper::

SQL> SELECT * FROM dual WHERE regexp_like('u', '[:upper:]');

DUMMY
-----
X

Answer 2

As and additional note to Vincent's answer this is a common regex pitfall. See e.g. Why is using a POSIX character class in my regex pattern giving unexpected results? - there you can just read Perl as Oracle SQL as the regex problem and solution are the same than in your case.

Here is also the same case illustrated with GNU grep:

$ echo E | grep -e '[:upper:]'
grep: character class syntax is [[:space:]], not [:space:]
$ echo E | grep -e '[[:upper:]]'
E
$

So nothing Oracle special here.