Как проверить подстроку в строке в Oracle без использования LIKE? Предположим, что я хочу выбрать всех пользователей из таблицы с буквой "z" в своей фамилии:
SELECT * FROM users WHERE last_name LIKE "%z%";
Это сработает, но я не хочу использовать LIKE. Есть ли какая-то другая функция, которую я мог бы использовать?
Я предполагаю, что причина, по которой вы спрашиваете, - это производительность? Там функция instr. Но это, вероятно, будет работать почти так же за кулисами.
Возможно, вы можете посмотреть в полнотекстовый поиск.
В качестве последних курортов вы будете искать кеширование или предварительно вычисленные столбцы/индексированное представление.
Если вас интересует только "z", вы можете создать индекс на основе функций.
CREATE INDEX users_z_idx ON users (INSTR(last_name,'z'))
Тогда ваш запрос будет использовать WHERE INSTR(last_name,'z') > 0.
При таком подходе вам нужно будет создать отдельный индекс для каждого символа, который вы, возможно, захотите найти. Я полагаю, что если вы часто это делаете, возможно, стоит создать один индекс для каждой буквы.
Кроме того, имейте в виду, что если ваши данные имеют имена, заглавные в стандартном порядке (например, "Zaxxon" ), то и ваш пример, и мой не будут соответствовать именам, начинающимся с Z. Вы можете исправить это включая LOWER в выражении поиска: INSTR(LOWER(last_name),'z').
Вы можете сделать это с помощью INSTR:
SELECT * FROM users WHERE INSTR(LOWER(last_name), 'z') > 0;
INSTR возвращает ноль, если подстрока не находится в строке.
Из интереса, почему вы не хотите использовать?
Изменить: я позволил сделать регистр поиска нечувствительным, поэтому вы не пропустите Bob Zebidee.: -)
Базы данных сильно оптимизированы для общих сценариев использования (и LIKE является одним из них).
Вы не найдете более быстрый способ выполнить поиск, если хотите остаться на уровне DB.
Имейте в виду, что для поиска этих значений стоит только что-либо, кроме полного сканирования таблицы, если количество блоков, содержащих строку, которая соответствует предикату, значительно меньше, чем общее количество блоков в таблице. Вот почему Oracle часто отказывается от использования индекса для полного сканирования, когда вы используете LIKE '% x%', где x - очень маленькая строка. Например, если оптимизатор считает, что использование индекса все равно потребует одноблочных чтений (скажем) на 20% блоков таблицы, то полное сканирование таблицы, вероятно, является лучшим вариантом, чем сканирование индекса.
Иногда вы знаете, что ваш предикат намного более избирателен, чем может оценить оптимизатор. В таком случае вы можете найти подсказку оптимизатора для быстрого сканирования индекса в соответствующем столбце (особенно если индекс намного меньше, чем таблица).
SELECT /*+ index_ffs(users (users.last_name)) */
*
FROM users
WHERE last_name LIKE "%z%"