Разработчик Oracle, часто использующий в коде регулярные выражения, особенно на базах с православными настройками, рано или поздно может столкнуться с явлением, которое, кроме как мистикой, никак не назовешь. Длительные поиски причин возникновения проблемы могут привести к потере веса, аппетита и спровоцировать различного рода психосоматические расстройства — все это я сейчас и попробую предотвратить. А поможет мне в этом функция regexp_replace. Она может иметь до 6 аргументов:
REGEXP_REPLACE (
- исходная_строка,
- шаблон,
- заменяющая_строка,
- позиция начала поиска совпадения с шаблоном (по умолчанию 1),
- номер вхождения шаблона в исходную строку (по умолчанию 0 – все вхождения),
- модификатор (пока что темная лошадка)
Возвращает измененную исходную_строку, в которой все вхождения шаблона заменены значением, переданным в параметре заменяющая_строка. Зачастую пользуются короткой версией функции, где заданы 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 alphabet 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
| SYMBOL | NLS_CODE_HEX |
|---|---|
| A | 14000100 |
| a | 14000200 |
| B | 19000100 |
| b | 19000200 |
| C | 1E000100 |
| c | 1E000200 |
| X | 7D000100 |
| x | 7D000200 |
| Y | 82000100 |
| y | 82000200 |
| Z | 87000100 |
| z | 87000200 |
В запросе указан 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
| ID | RESULT |
|---|---|
| 1 | A************************* |
| 2 | *************************z |
| 3 | А***************************** |
| 4 | *****************************я |
Лечение.
Явно указывать модификатор регистрозависимости
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.
А пока все. Доброго здоровья.