CODEWITHSUNDEEP
oracle-databasenlsregexp-likeoracle-textnls-lang
Chris
Chris

Reputation: 9

Oracle REGEXP_LIKE ignore CASE SENSITIVITY

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

1

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

Upvotes: -1

Views: 1394

Answers (1)

Chris
Chris

Reputation: 9

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.

Upvotes: 1

Related Questions