0

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

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
Pato
  • 153
  • 6
  • I get "oh" in [this db Fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=0eef7f0c00666d7ef267b01e892127c1) – GMB Sep 20 '20 at 20:19
  • Not a solution, but what happens if you try `'[^A-Z]{2}'`? – bvj Sep 20 '20 at 20:39
  • 2
    Review this page regarding NLS_SORT https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch5lingsort.htm – bvj Sep 20 '20 at 20:47

1 Answers1

3

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');
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53