Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Hello I am trying to implement a some simple oracle queries, and i was testing the usage of regexp_count.

I ran into this weird case, the code:

select regexp_count('OOO', '[a-z0-9]', 1,'c') from dual;
This returns 3 (1 match per character), when it should return 0. It seems that it is ignoring the flag 'c' (case sensitivity), and matching the 'O' as an 'o'.
Is this the intended behavior or am I missing something?
                are you sure that your pattern is not '[a-zA-Z0-9]' , or the value contains three zeroes instead of the three letters "O" ..? (it's working properly)
– Barbaros Özhan
                Oct 14, 2020 at 11:22
                what on earth it must be a missconfiguration with my oracle, I am using 12.2.0.1.0 not sure if it matters @GordonLinoff
– João Ramiro
                Oct 14, 2020 at 11:26
                What locale are you using, and what is NLS_SORT set to in your session? The 'c' match parameter should probably make that irrelevant, but still... You can possibly avoid the issue by using '[[:lower:]0-9]', or '[[:lower:][:digit:]]', and might not need the 'c' in that case.
– Alex Poole
                Oct 14, 2020 at 11:46
WHERE  parameter IN ( 'NLS_COMP', 'NLS_SORT' );
And see what NLS session parameters you are using.
For example:
ALTER SESSION SET NLS_COMP=BINARY;
ALTER SESSION SET NLS_SORT=BINARY;
SELECT regexp_count('OOO', '[a-z0-9]', 1, 'c' ) AS COUNT_AZ,
       regexp_count('OOO', '[[:lower:][:digit:]]', 1, 'c' ) AS count_lower
FROM   DUAL;
Outputs:
COUNT_AZ | COUNT_LOWER
-------: | ----------:
       0 |           0
ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=GERMAN_AI;
-- or ALTER SESSION SET NLS_SORT=GERMAN_CI;
-- or ALTER SESSION SET NLS_SORT=FRENCH_CI;
-- or many others
SELECT regexp_count('OOO', '[a-z0-9]', 1, 'c' ) AS COUNT_AZ,
       regexp_count('OOO', '[[:lower:][:digit:]]', 1, 'c' ) AS count_lower
FROM   DUAL;
Outputs:
COUNT_AZ | COUNT_LOWER
-------: | ----------:
       3 |           0
To fix this, you can either:
Change the NLS_SORT session parameter.
Or (as mentioned by @AlexPoole) use the [:lower:] POSIX character-set instead of a-z.
db<>fiddle here
                Thanks, my NLS_SORT was Western_European, changing it to BINARY solved it! Much appreciated
– João Ramiro
                Oct 14, 2020 at 15:05
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.