Регистр символов часто играет важную роль в работе со строками. Например, может потребоваться сравнить две строки независимо от регистра. Выбор решения этой проблемы зависит как от версии базы данных, так и от области действия выполняемых операций.
Преобразование строки к верхнему или нижнему регистру
Для решения проблем с регистром символов можно воспользоваться встроенной функцией UPPER или LOWER. Эти функции преобразуют всю строку за одну операцию. Пример:
DECLARE
name1 VARCHAR2(30) := 'Andrew Sears';
name2 VARCHAR2(30) := 'ANDREW SEARS';
BEGIN
IF LOWER(name1) = LOWER(name2) THEN
DBMS_OUTPUT.PUT_LINE('Имена совпадают.');
END IF;
END;
В этом примере обе строки обрабатываются функцией LOWER, так что в итоговом сравнении участвуют строки 'andrew sears' и 'andrew sears'.
Сравнение без учета регистра символов
Начиная с Oracle10g Release 2, появилась возможность использования параметров инициализации NLS_COMP и NLS_SORT для включения режима сравнения без учета регистра. Задайте параметру NLS_COMP значение LINGUISTIC; тем самым вы прикажете Oracle использовать NLS_SORT для сравнений строк. Затем задайте параметру NLS_SORT значение, соответствующее сравнению без учета регистра — например, BINARY_CI или XWEST_EUROPEAN_CI. (Суффикс _CI означает «Case Insensitivity», то есть «Без учета регистра».) Приведенный далее простой пример показывает, какие проблемы решаются при помощи NLS_COMP. Требуется взять список имен и определить, какое из них должно стоять на первом месте:
SELECT LEAST ('JONATHAN','Jonathan','jon') FROM dual
В моей системе этот вызов LEAST возвращает строку 'JONATHAN'. Дело в том, что в порядке сортировки символы верхнего регистра предшествуют символам нижнего регистра. По умолчанию параметру NLS_COMP задается значение BINARY, при котором результат строковых сравнений, выполняемых такими функциями, как LEAST, определяется кодами символов.
Возможно, вы предпочитаете, чтобы функция LEAST игнорировала регистр символов и возвращала 'jon' вместо 'JONATHAN'. Измените значение NLS_COMP, чтобы при сортировке учитывалось значение NLS_SORT:
ALTER SESSION SET NLS_COMP=LINGUISTIC
Теперь необходимо изменить NLS_SORT с указанием нужных правил сортировки.
По умолчанию переменная NLS_SORT часто равна BINARY, но значение может быть и другим в зависимости от конфигурации системы. В нашем примере будет использоваться сортировка BINARY_CI:
ALTER SESSION SET NLS_SORT=BINARY_CI
Попробуем снова вызвать LEAST:
SELECT LEAST ('JONATHAN','Jonathan','jon') FROM dual На этот раз будет получен результат 'jon'. Пример кажется простым, но добиться такого результата без только что описанной сортировки по правилам языка будет нелегко.
Действие языковой сортировки распространяется не только на функции, но и на простые сравнения строк. Пример:
BEGIN
IF 'Jonathan' = 'JONATHAN' THEN
DBMS_OUTPUT.PUT_LINE('It is true!');
END IF;
END;
При указанных значениях параметров NLS_COMP и NLS_SORT выражение 'Jonathan' = 'JONATHAN' в этом примере равно TRUE.
Параметры NLS_COMP и NLS_SORT влияют на все операции со строками. Они продолжают действовать вплоть до их явного изменения или до завершения сеанса.
Oracle также поддерживает режим сортировки без учета диакритических знаков; чтобы включить его, присоедините к имени правила сортировки суффикс _AI (вместо _CI).
За полным списком правил языковой сортировки обращайтесь к документации Oracle Database Globalization Support Guide. В этом руководстве также подробно объясняется действие параметров NLS_COMP и NLS_SORT.
Регистр символов и индексы
При работе со строками часто возникает необходимость в поиске и сравнении без учета регистра символов. Но если вы реализуете описанный здесь прием, вдруг выясняется, что ваше приложение перестает использовать индексы и начинает работать слишком медленно. Будьте внимательны, чтобы ваши действия не повредили использованию индексов в SQL. Для наглядности рассмотрим пример с демонстрационной таблицей hr.employees. Таблица employees использует индекс emp_name_ix для столбцов last_name, first_name. Мой код включает следующую команду SQL:
SELECT * FROM employees WHERE last_name = lname
Изначально код использует индекс emp_name_ix, но когда я задаю параметры NLS_COMP=LINGUISTIC и NLS_SORT=BINARY_CI, чтобы включить поиск без учета регистра, индекс перестает использоваться, и операции выполняются с полным просмотром таблицы — со всеми вытекающими последствиями! Одно из возможных решений заключается в использовании индекса на базе функции, игнорирующего регистр символов:
CREATE INDEX last_name_ci ON EMPLOYEES (NLSSORT(last_name, 'NLS_SORT=BINARY_CI'))
Теперь при выполнении запросов без учета регистра символов используется индекс без учета регистра, а быстродействие программы остается на высоком уровне.
Преобразование первого символа к верхнему регистру
Кроме функций UPPER и LOWER, для преобразования регистра символов используется функция INITCAP. Она преобразует первую букву каждого слова в строке к верхнему регистру, а все остальные буквы — к нижнему регистру. Например, следующий фрагмент:
DECLARE name VARCHAR2(30) := 'MATT williams'; BEGIN DBMS_OUTPUT.PUT_LINE(INITCAP(name)); END;
выводит следующий результат:
Matt Williams
Идея использовать INITCAP для форматирования имен выглядит заманчиво, и все будет хорошо, пока вы не столкнетесь с особым случаем:
DECLARE name VARCHAR2(30) := 'JOE mcwilliams'; BEGIN DBMS_OUTPUT.PUT_LINE(INITCAP(name)); END;
Результат:
Joe Mcwilliams
Правильное написание фамилии — «McWilliams», а не «Mcwilliams». Помните, что функция INITCAP временами удобна, но она выдает неверный результат для имен и слов, которые содержат более одной буквы в верхнем регистре.
