weird behavior of regexp functions in oracle 19c

Question

I am not an expert in regexp, but as default should be case sensitive on. I try to figure it out why the regexp gives me the first 2 characters when the first one is capital

select regexp_substr('JohnFoo','[a-z]{2}') from dual;

the output is 'Jo'

if I tried this

select regexp_substr('JohnFoo','^[a-z]{2}') from dual;

the output is the same but it should recognize 2 non capital letters at the beginning so in this case no match

then I tried the same with all arguments

select regexp_substr('JohnFoo','[a-z]{2}',1,1,'c') from dual;

again the output is 'Jo' instrad of 'oh'

what am I doing wrong?this behavior is same fot substr ,replace as well. it seems to me like ignoring the capitals

Answer 1

You should first check the NLS setting in your session. The behaviour you expect is provided with the BINARY sort, which you probably do no have assigned.

Below a little example

ALTER SESSION SET NLS_COMP=BINARY;
ALTER SESSION SET NLS_SORT=BINARY;

select regexp_substr('JohnFoo','[a-z]{2}') from dual;

RE
--
oh

ALTER SESSION SET NLS_COMP=LINGUISTIC;
ALTER SESSION SET NLS_SORT=BINARY_CI;

select regexp_substr('JohnFoo','[a-z]{2}') from dual;

RE
--
Jo

To get your actual session values use the query

select * from NLS_SESSION_PARAMETERS 
where parameter in ('NLS_COMP','NLS_SORT');