Oracle regex - encoding issue

Question

In an Oracle 10 database, I did a small test:

SELECT REGEXP_REPLACE('İF', '[Iİ]F', 'DONE', 1, 0, 'i') FROM dual;

This does not seem to match the regex. However, when I remove the last parameter (case insensitive regex parameter), the regex matches:

SELECT REGEXP_REPLACE('İF', '[Iİ]F', 'DONE', 1, 0) FROM dual;

Below queries also returns "DONE":

SELECT REGEXP_REPLACE('IF', '[Iİ]F', 'DONE', 1, 0) FROM dual;
SELECT REGEXP_REPLACE('iF', '[Iİ]F', 'DONE', 1, 0, 'i') FROM dual;

Character set of the database is UTF8.

What should I do to make this regex work?

PS: In Turkish language, uppercase version of the character "i" is "İ".

Vincent Malgrat · Accepted Answer · 2009-09-03 16:16:47Z

You could use the Equivalence Class [[=i=]]:

SQL> select regexp_replace('İF', '[[=i=]]f', 'DONE', 1, 0, 'i') from dual;

REGEXP_REPLACE('İF','[[=I=]]F'
------------------------------
DONE

SQL> select regexp_replace('if', '[[=i=]]f', 'DONE', 1, 0, 'i') from dual;

REGEXP_REPLACE('IF','[[=I=]]F'
------------------------------
DONE

Edit: There seems to be a bug in some versions of Oracle with the REGEXP search functions regarding ı->I and i->İ

One possible workaround is to use the NLS_UPPER or NLS_LOWER functions that work reliably with the correct NLS_SORT setting:

SQL> alter session set nls_sort=XTURKISH;

Session altered

SQL> select regexp_replace(NLS_UPPER('İF'), 'İF', 'DONE', 1, 0) from dual;

REGEXP_REPLACE(NLS_UPPER('İF'
------------------------------
DONE

SQL> select regexp_replace(NLS_UPPER('if'), 'İF', 'DONE', 1, 0) from dual;

REGEXP_REPLACE(NLS_UPPER('IF')
------------------------------
DONE

Collectives™ on Stack Overflow

Oracle regex - encoding issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related