Oracle regular expression using hyphen in pattern

Question

Why is this query returning the value 4 (I expected 0)?

select  regexp_instr ('123abc','[A-Z]')
from dual;

I think [] should indicate a character list, and A-Z includes all upper letters?

Answer 1

This is affected by your session's NLS_SORT setting, and you will get a result of 4 if you have case-insensitive sorting enabled:

alter session set nls_sort=binary;
select  regexp_instr ('123abc','[A-Z]')
from dual;

REGEXP_INSTR('123ABC','[A-Z]')
------------------------------
                             0

alter session set nls_sort=binary_ci;
select  regexp_instr ('123abc','[A-Z]')
from dual;

REGEXP_INSTR('123ABC','[A-Z]')
------------------------------
                             4

You can read more in the documentation; and you may find this answer useful too.