Why does the REGEXP_LIKE function treat the letter "e" as uppercase instead of a lowercase letter?

Question

In my pl / sql script, Oracle treats the letter "e" as uppercase when it is searched using the syntax of the [: upper:] Character class.

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

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. it works because u contained in the string :upper: ::

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

Answer 2

Like the additional note to Vincent's answer, this is a common mistake in a regular expression. See Why using the POSIX character class in my regex pattern gives unexpected results? - there you can just read Perl as Oracle SQL, because the problem and solution of regular expressions are the same as in your case.

This also shows the same case as in GNU grep:

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

So, there is nothing special here.