Oracle REGEXP_LIKE ignore CASE SENSITIVITY

Question

We are trying the following request in Oracle Database 19.3 and the result is ever like the case sensitivity was ignores.

select 1 from dual where regexp_like('CHIEN', '[a-z]+', 'c');

And the result of the request is

Even with:

ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=GERMAN_AI;

We are thinking that's come from a NLS parameter but we don't found which one.

We have those parameters:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_calendar                         string      GREGORIAN
nls_comp                             string      BINARY
nls_currency                         string      €
nls_date_format                      string      DD/MM/RR
nls_date_language                    string      FRENCH
nls_dual_currency                    string      €
nls_iso_currency                     string      FRANCE
nls_language                         string      FRENCH
nls_length_semantics                 string      BYTE
nls_nchar_conv_excp                  string      FALSE
nls_numeric_characters               string      ,

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_sort                             string      FRENCH
nls_territory                        string      FRANCE
nls_time_format                      string      HH24:MI:SSXFF
nls_timestamp_format                 string      DD/MM/RR HH24:MI:SSXFF
nls_timestamp_tz_format              string      DD/MM/RR HH24:MI:SSXFF TZR
nls_time_tz_format                   string      HH24:MI:SSXFF TZR

And our NLS_LANG is set to French_France.AL32UTF8

The third parameter apply the case sensitive or insensitive. If you put C it is sensitive, you must to put 'i' — Vahram Danielyan, Jun 08 '21 at 07:02
Yes, change your NLS sort/comp settings from being case insensitive to being case sensitive and it should fix the issue. — MT0, Jun 08 '21 at 08:52
it's not fix the issue. With regexp_count it's ok, but not with regexp_like — Chris, Jun 08 '21 at 09:15
Perhaps this post https://stackoverflow.com/questions/57986830/oracle-regexp-instr-and-a-z-character-range-doesnt-match-as-expected will help you. — Kapitany, Jun 08 '21 at 10:34

score 1 · Answer 1 · answered Jun 09 '21 at 06:59

Finally I found the solution.

The problem was with NLS_SORT=FRENCH (or any linguistic sort)

NLS_SORT=FRENCH => AaBbCcDdEeFfGgHhIiJjKkLlMmNn .... Z

And when we are lookup for regular expression [a-z], we include BCDE ... Z

The solution is to set NLS_SORT=BINARY

In my case, I set environment variable NLS_SORT=BINARY. Without that, Oracle take the default NLS_SORT value that correspond to NLS_LANG.

Oracle REGEXP_LIKE ignore CASE SENSITIVITY

1 Answers1