Добро пожаловать!
Здесь вы можете найти ответ на интересующий вас вопрос в отрасли сайтостроения, познакомится ближе с web технологиями и web стандартами.

Учебник SQL

Урок 6. Использование метасимволов для фильтрации

В этом уроке вы узнаете, что такое метасимволы, как их использовать и как выполнять поиск с применением метасимволов и логического оператора LIKE для фильтрации выводимых данных.

Использование логического оператора LIKE

Все предыдущие операторы, которые мы рассмотрели, производили фильтрацию по известным значениям. Они искали совпадения по одному или нескольким значениям, более чем и менее чем известное значение или диапазон значений. При этом везде искалось известное значение. Однако фильтрация данных таким способом не всегда работает. Например, как бы вы искали продукты, в названии которых содержатся слова bean bag? Этого нельзя сделать при помощи простых операций сравнения, здесь на помощь приходит поиск с использованием метасимволов. При помощи метасимволов можно создавать условия поиска данных. В этом примере, для того чтобы найти все продукты, в названии которых содержатся слова bean bag, необходимо составить шаблон поиска, позволяющий найти текст bean bag в любом месте названия продукта.

Метасимволы

Специальные символы, применяемые для поиска части значения.

Шаблон поиска

Условие поиска, состоящее из текста, метасимволов и любой их комбинации.

Метасимволы сами по себе являются символами, которые имеют в условии WHERE специальное значение. В SQL поддерживаются метасимволы нескольких типов. Чтобы применять метасимволы в условиях поиска, необходимо использовать ключевое слово LIKE. Оно сообщает СУБД, что следующий шаблон для поиска необходимо сравнивать с использованием метасимволов, а не искать точные совпадения.

Предикат

Когда оператор не является оператором? Тогда, когда он является предикатом. Технически, LIKE – это предикат, а не оператор. Конечный результат остается тем же, просто не пугайтесь этого термина, если вы встретите его в документации по SQL.

Поиск с использованием метасимволов может осуществляться только в текстовых полях (строках), нельзя использовать метасимволы при поиске полей с нетекстовым типом данных.

Метасимвол "знак процента" (%)

Наиболее часто используемый метасимвол – знак процента (%). В строке поиска % означает найти все вхождения любого символа. Например, чтобы найти все продукты, названия которых начинаются со слова Fish, можно выполнить следующий запрос:

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

Вывод:

prod_id  prod_name
--------------------------
BNBG01   Fish bean bag toy

В этом примере используется шаблон поиска 'Fish%'. При выполнении этого условия возвращаются все значения, которые начинаются с символов Fish. Знак % указывает СУБД принимать все символы после слова Fish независимо от их количества.

Метасимволы Microsoft Access

Если вы работаете в Microsoft Access, необходимо использовать символ * вместо символа %.

Зависимость от регистра

Ваша СУБД и ее конфигурация могут влиять на то, что поиск будет зависеть от регистра. В этом случае по строке 'fish%' значение Fish bean bag toy не будет найдено.

Метасимволы можно использовать в любом месте шаблона поиска, причем в неограниченном количестве. В следующем примере используются два метасимвола, по одному на каждом конце шаблона.

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';

Вывод:

prod_id  prod_name
--------------------------
BNBG01   Fish bean bag toy
BNBG02   Bird bean bag toy
BNBG03   Rabbit bean bag toy

Шаблон поиска '%bean bag%' означает найти все значения, содержащие bean bag в любом месте названия, независимо от количества символов перед или после указанного текста.

Метасимвол можно также использовать внутри шаблона поиска, хотя это редко бывает полезным. В следующем примере производится поиск всех продуктов, которые начинаются на F и заканчиваются на y:

SELECT prod_name
FROM Products
WHERE prod_name LIKE 'F%y';

Важно отметить, что помимо поиска одного или нескольких символов, знак % также означает и отсутствие символов в указанном месте шаблона поиска.

Следите за замыкающими пробелами

Многие СУБД, включая Microsoft Access, заполняют содержимое поля пробелами. Например, если столбец рассчитан на 50 символов, а в нем вставлен текст Fish bean bag toy (17 символов), то, чтобы заполнить столбец, в него может быть добавлено еще 33 пробела. Обычно это не влияет на данные или их использование, но может негативно отразиться на предыдущем SQL-выражении. По условию WHERE prod_name LIKE 'F%y' будут найдены только те значения prod_name, которые начинаются на F и заканчиваются на y. Если значение заполнено пробелами, оно не будет заканчиваться на y, и значение Fish bean bag toy не будет извлечено. Одним из простых решений может быть добавление второго символа % в шаблон поиска: 'F%y%', после чего будут учитываться символы (пробелы) после буквы y. Но лучше "отрезать" пробелы при помощи функций, которые обсуждаются в уроке 8, "Использование функций манипулирования данными".

Метасимвол "символ подчеркивания" (_)

Еще одним полезным метасимволом является символ подчеркивания (_). Символ подчеркивания используется так же, как и %, но при этом учитывается не много символов, а только один.

Метасимволы в Microsoft Access

Если вы работаете в Microsoft Access, вам нужно использовать знак ? вместо символа _.

Взгляните на этот пример.

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

Следите за замыкающими пробелами

Как и в предыдущем примере, возможно, понадобится добавить метасимвол % в шаблон, чтобы пример работал.

Вывод:

prod_id  prod_name
---------------------------
BNBG02   12 inch teddy bear
BNBG03   18 inch teddy bear

В шаблоне поиска этого предложения WHERE использованы два метасимвола, затем следует текст. В результате были выбраны только те строки, которые удовлетворяли шаблону поиска: по двум символам подчеркивания было найдено число 12 в первой строке и 18 во второй. Продукт 8 inch teddy bear не был найден, так как в шаблоне поиска требуется два совпадения, а не одно. Для сравнения, в следующем выражении SELECT используется метасимвол %, вследствие чего извлекаются три названия товара:

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '% inch teddy bear';

Вывод:

prod_id  prod_name
---------------------------
BNBG01   8 inch teddy bear 
BNBG02   12 inch teddy bear 
BNBG03   18 inch teddy bear

В отличие от знака %, который подразумевает также отсутствие символов, знак _ всегда означает один символ – не более и не менее.

Метасимвол "квадратные скобки" ([ ])

Метасимвол "квадратные скобки" ([ ]) используется для указания набора символов, каждый из которых должен совпадать со значением, причем точно в указанном месте (в местоположении метасимвола).

Наборы не всегда поддерживаются

В отличие от метасимволов, описанных ранее, использование квадратных скобок для создания наборов многими СУБД не поддерживается. Наборы поддерживаются в СУБД Microsoft Access, Microsoft SQL Server и Sybase Adaptive Server. Обратитесь к документации по вашей СУБД, чтобы определить, поддерживаются ли в ней наборы.

Например, чтобы найти все контакты людей, имена которых начинаются на букву J или M, необходимо сделать следующее:

SELECT prod_id, prod_name 
FROM Customers
WHERE cust_contact LIKE '[JM]%' 
ORDER BY cust_contact;

Вывод:

cust_contact
--------------
Jim Jones 
John Smith 
Michelle Green

Условие WHERE в этом выражении выглядит как '[JM]%'. В этом шаблоне поиска используются два разных метасимвола. По метасимволам [JM] производится поиск всех контактных лиц, имена которых начинаются на одну из указанных в скобках букв, но при этом учитывается только один символ. Поэтому все имена длиннее одного символа не будут извлечены. По метасимволу %, следующему после [JM], производится поиск любого количества символов после первой буквы, что и приводит к требуемому результату.

Можно использовать метасимвол, выполняющий противоположное действие, добавив перед ним символ ^. Например, в следующем примере выбираются все имена, которые не начинаются с буквы J или М (в отличие от предыдущего примера):

SELECT prod_id, prod_name 
FROM Customers
WHERE cust_contact LIKE '[^JM]%' 
ORDER BY cust_contact;

Противоположные наборы в Microsoft Access

Если вы работаете в Microsoft Access и требуется создать противоположный набор, необходимо использовать символ ! вместо ^, поэтому указывайте [!JM], а не [^JM].

Конечно, можно достичь того же результата, воспользовавшись логическим оператором NOT. Единственным преимуществом символа ^ является более простой синтаксис при выполнении нескольких предложений WHERE.

Внимание!

Метасимвол ([ ]) поддерживается не всеми СУБД. Обратитесь к документации по вашей СУБД, чтобы определить, поддерживается ли этот метасимвол.

Советы по использованию метасимволов

Как видите, метасимволы в SQL – это очень мощный механизм. Но за эту мощь приходится платить: поиск с использованием метасимволов требует больше времени на обработку, чем любые другие виды поиска, которые мы обсуждали ранее. Ниже приведены несколько советов по использованию метасимволов.

  • Не злоупотребляйте метасимволами. Если можно использовать другой оператор поиска, воспользуйтесь им.
  • При использовании метасимволов старайтесь по возможности не вставлять их в начало шаблона поиска. Шаблоны поиска, начинающиеся с метасимволов, обрабатываются медленнее всего.
  • Внимательно следите за местоположением метасимволов. Если они находятся не на своем месте, будут извлечены не те данные.

Исходя из всего вышесказанного, можно заключить, что метасимволы очень важны и очень полезны при поиске – вы часто будете ими пользоваться.

Резюме

В этом уроке рассказывалось о том, что такое метасимволы и как их использовать в условиях WHERE. Теперь вы знаете, что метасимволы нужно использовать осторожно, не следует злоупотреблять ими.