Регулярные выражения Oracle. Опасный диапазон

Оракловый разработчик, часто использующий в коде регулярные выражения, особенно на базах с православными настройками, рано или поздно может столкнуться с явлением, которое, кроме как мистикой, не назовешь. Длительные поиски причин возникновения проблемы могут привести к потере веса, аппетита и спровоцировать различного рода психосоматические расстройства, что я и попробую предотвратить. А поможет мне в этом функция regexp_replace. Она может иметь до 6 аргументов:

REGEXP_REPLACE (

1. исходная_строка,
2. шаблон,
3. заменяющая_строка,
4. позиция начала поиска совпадения с шаблоном (по умолчанию 1),
5. номер вхождения шаблона в исходную строку (по умолчанию 0 – все вхождения),
6. модификатор (темная лошадка)

)
Возвращает измененную исходную_строку, в которой все вхождения шаблона заменены значением, переданным в параметре заменяющая_строка. Зачастую пользуются короткой версией функции, где заданы 3 первых аргумента, что бывает достаточно для решения многих задач. Я тоже так сделаю. Допустим, нам нужно в строке ‘MASK: lower case’ замаскировать все строчные символы звездочками. Для задания диапазона строчных символов должен подойти шаблон ‘[a-z]’. Проверяем

select regexp_replace('MASK: lower case', '[a-z]', '*') as result from dual 

Ожидание

+------------------+ | RESULT           | +------------------+ | MASK: ***** **** | +------------------+ 

Реальность

+------------------+ | RESULT           | +------------------+ | *A**: ***** **** | +------------------+ 

Если на вашей базе это явление не воспроизвелось, значит, пока повезло. Но чаще начинаются копания в кодировках, конвертации строк из одного набора символов в другой и со временем наступает примерно такое состояние

Постановка диагноза.

Возникает вопрос – что же такого особенного в букве ‘А’, что она не заменилась, ведь остальные прописные символы тоже не должны были. Может, кроме нее есть еще какие-то правильные буквы? Надо смотреть весь алфавит прописных символов.

select regexp_replace('ABCDEFJHIGKLMNOPQRSTUVWXYZ', '[a-z]', '*') as result from dual  +----------------------------+ | ALPHABET                   | +----------------------------+ | A************************* | +----------------------------+ 

Однако.

А прикол вот в чем. Если явно не задан 6-й аргумент функции – модификатор, например, ‘i’ – регистронезависимость, или ‘с’ – регистрозависимость при сравнении исходной строки с шаблоном, то регулярное выражение по умолчанию использует NLS_SORT параметр сессии / базы. У меня он такой:

select value from sys.nls_session_parameters where parameter = 'NLS_SORT'  +---------+ | VALUE   | +---------+ | RUSSIAN | +---------+ 

Этот параметр задает способ сортировки в ORDER BY. Если речь идет о сортировке простых одиночных символов, то каждому из них в двоичном представлении соответствует некоторое число (NLSSORT–код) и сортировка на самом деле происходит по величине этих чисел.

Для наглядного примера возьмем несколько первых и несколько последних символов алфавита, как строчных, так и прописных, и поместим их в условно неупорядоченный табличный набор, назовем его ABC. Затем отсортируем этот набор по полю SYMBOL, и рядом с каждым символом отобразим его NLSSORT–код в HEX формате.

with ABC as (   select column_value as symbol   from table(sys.odcivarchar2list('A','B','C','X','Y','Z','a','b','c','x','y','z')) ) select symbol,         nlssort(symbol) nls_code_hex from ABC order by symbol 

В запросе указан ORDER BY по полю SYMBOL, но по факту в базе сортировка прошла по значениям из поля NLS_CODE_HEX.

Теперь вернемся к диапазону из шаблона и посмотрим на таблицу – что находится по вертикали между символом ‘a’ (код 14000200) и ‘z’ (код 87000200)? Все, кроме строчной буквы ‘A’. Вот это все звездочкой и заменилось. А код 14000100 буквы ‘A’ в диапазон замены от 14000200 до 87000200 не попал.

С кириллицей такая же история. Ниже запрос с подобными результатами, их причины теперь понять не сложно.

select 1 id, regexp_replace('ABCDEFJHIGKLMNOPQRSTUVWXYZ', '[a-z]', '*') result from dual union all select 2, regexp_replace('abcdefjhigklmnopqrstuvwxyz', '[A-Z]', '*') from dual union all select 3, regexp_replace('АБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЭЮЯ', '[а-я]', '*') from dual union all select 4, regexp_replace('абвгдеёжзийклмнопрстуфхцчшщэюя', '[А-Я]', '*') from dual 

Лечение.

Явно указывать модификатор регистрозависимости

select regexp_replace('MASK: lower case', '[a-z]', '*', 1, 0, 'c') from dual  +------------------+ | RESULT           | +------------------+ | MASK: ***** **** | +------------------+ 

В некоторых источниках пишут, что модификатор ‘c’ задается по умолчанию, но только что мы увидели, что это не совсем так. А если кто не увидел, значит NLS_SORT параметр его сессии / базы скорее всего установлен в BINARY и сортировка идет по соответствию с реальными кодами символов. Действительно, если изменить параметр сессии, проблема уйдет.

ALTER SESSION SET NLS_SORT=BINARY;  select regexp_replace('MASK: lower case', '[a-z]', '*') as result from dual  +------------------+ | RESULT           | +------------------+ | MASK: ***** **** | +------------------+ 

Тесты, если что, проводились в Oracle 12c.

А пока все. Доброго здоровья.

ссылка на оригинал статьи http://habrahabr.ru/post/269387/