Sql escape символы

Обновлено: 05.07.2024

В предикате Like знак процента (%) соответствует нулю или нескольким символам, а символ подчеркивания (_) соответствует любому одному символу. Чтобы сопоставить фактический знак процента или символ подчеркивания в предикате Like , перед знаком процента или символом подчеркивания должна быть escape-последовательность. Escape-символ предиката LIKE, который определяет подобную последовательность:

где escape-символ — любой символ, поддерживаемый источником данных.

дополнительные сведения о escape-последовательности like см. в разделе like escape-последовательность в приложении C: SQL грамматики.

например, следующие инструкции SQL создают тот же результирующий набор имен клиентов, которые начинаются с символов «% AAA». В первой инструкции используется синтаксис escape-последовательности. Вторая инструкция использует собственный синтаксис для Microsoft® Access и не поддерживает взаимодействие. Обратите внимание, что второй символ процента в каждом предикате Like является символом-шаблоном, который соответствует нулю или большему числу символов.

Чтобы определить, поддерживается ли в источнике данных escape-символ предиката Like , приложение вызывает SQLGetInfo с параметром SQL_LIKE_ESCAPE_CLAUSE.

Oracle условие LIKE позволяет использовать подстановочные символы, которые будут использоваться в операторе WHERE в запросах SELECT, INSERT, UPDATE или DELETE. Это позволяет выполнять сопоставление с pattern (шаблоном).

Аргументы

match_expression
Любое допустимое выражение expression символьного типа данных.

pattern
Конкретная строка символов для поиска в match_expression может содержать следующие допустимые символы-шаблоны. Длина значения pattern не может превышать 8000 байт.

Символ-шаблон Описание Пример
% Любая строка, содержащая ноль или более символов. Инструкция WHERE Название LIKE '%компьютер%' выполняет поиск и выдает все названия книг, содержащие слово «компьютер».
_ (подчеркивание) Любой одиночный символ. Инструкция WHERE фамилия_автора LIKE '_етров' выполняет поиск и выдает все имена, состоящие из шести букв и заканчивающиеся сочетанием «етров» (Петров, Ветров и т.п.).
[ ] Любой одиночный символ, содержащийся в диапазоне ([a-f]) или наборе ([abcdef]). Инструкция WHERE Фамилия_автора LIKE '[Л-С]омов' выполняет поиск и выдает все фамилии авторов, заканчивающиеся на «омов» и начинающиеся на любую букву в промежутке от «Л» до «С», например Ломов, Ромов, Сомов и т.п. При выполнении операции поиска в диапазоне символы, включенные в диапазон, могут изменяться в зависимости от правил сортировки параметров сортировки.
[^] Любой одиночный символ, не содержащийся в диапазоне ([^a-f]) или наборе ([^abcdef]). Инструкция WHERE Фамилия_автора LIKE 'ив[^а]%' выполняет поиск и выдает все фамилии, начинающиеся на "ив", в которых третья буква отличается от "а".

escape_character
Символ, помещаемый перед символом-шаблоном для того, чтобы символ-шаблон рассматривался как обычный символ, а не как шаблон. Аргумент escape_character является символьным выражением, не имеющим значения по умолчанию и возвращающим результат в виде одного символа.

Пример использования ESCAPE

Важно понять, каким образом действует escape_character при совпадении с шаблоном. Эти примеры относятся конкретно к пропуску символов в Oracle.

Допустим, вы хотите найти % или _ (символ процента или подчеркивания) в операторе LIKE. Вы можете сделать это с помощью ESCAPE символов.

Обратите внимание, что вы можете определить escape_character (экранирующий символ), как один символ (длина 1).

Этот пример LIKE идентифицирует символ ! как экранирующий символ. Этот запрос вернет всех suppliers , чье supplier_name имеют значение 'Water%’.

Совпадение с шаблоном с помощью предложения ESCAPE

Можно искать символьные строки, в состав которых входит один или более специальных символов-шаблонов. Например, таблица discounts базы данных customers может содержать значения скидок, включающих знак процента (%). Чтобы выполнить поиск знака процента в качестве символа-шаблона, необходимо ввести ключевое слово ESCAPE и escape-символ. Например, образец базы данных содержит столбец с именем comment, в котором хранится значение «30%». Чтобы найти строки, содержащие последовательность символов «30%» в столбце comment, необходимо указать предложение WHERE, например WHERE comment LIKE '%30!%%' ESCAPE '!' . Если предложение ESCAPE и escape-символ не указаны, компонент Компонент Database Engine вернет все записи, содержащие последовательность символов "30!".

Если в шаблоне LIKE после escape-символа нет никакого символа, то шаблон является недопустимым и оператор LIKE возвращает значение FALSE. Если символ после escape-символа не является символом-шаблоном, то escape-символ игнорируется, а следующий символ рассматривается как обычный символ в шаблоне. К этим символам-шаблонам относятся: подчеркивание (_), процент (%) и левая квадратная скобка ([), в том случае, если они заключены в квадратные скобки. Escape-символы могут использоваться в квадратных скобках ([ ]), включая: знак вставки (^), дефис (-) и правую квадратную скобку (]).

Символ 0x0000 (char(0)) не определен в параметрах сортировки Windows, и его нельзя включать в LIKE.

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

Д. Применение оператора LIKE с символом-шаблоном %

В следующем примере в таблице DimEmployee выполняется поиск всех сотрудников, телефонные номера которых начинаются с 612 .

Е. Применение оператора NOT LIKE с символом-шаблоном %

В следующем примере в таблице DimEmployee выполняется поиск всех телефонных номеров, которые не начинаются с 612 . .

Ж. Применение оператора LIKE с символом-шаблоном _

В следующем примере в таблице DimEmployee выполняется поиск всех телефонных номеров, начинающихся с 2 и заканчивающихся на 6 . Подстановочный знак "%" добавлен в конце шаблона поиска, что соответствует любым следующим символам в значениях столбца с телефонными номерами.

Примеры

A. Применение оператора LIKE с символом-шаблоном %

В следующем примере в таблице 415 выполняется поиск всех телефонных номеров с кодом города PersonPhone .

Б. Применение оператора NOT LIKE с символом-шаблоном %

В следующем примере в таблице PersonPhone выполняется поиск всех телефонных номеров с региональным кодом, отличным от 415 .

В. Применение предложения ESCAPE

В следующем примере предложение ESCAPE и escape-символ используются для поиска символьной строки 10-15% в столбце c1 таблицы mytbl2 .

Г. Использование символов-шаблонов [ ]

В следующем примере выполняется поиск в таблице Person сотрудников с именем Cheryl или Sheryl .

В следующем примере выполняется поиск строк в таблице Person для сотрудников с фамилией Zheng или Zhang .

Пример использования % (символ процент)

Первый пример Oracle оператора LIKE, который мы рассмотрим, предполагает использование подстановочного символа % .

Рассмотрим, как % работает в Oracle операторе LIKE. Мы хотим найти всех customers , чьи last_name начинается с 'Ар'.

Кроме того, можно использовать несколько символов % в пределах одной строки.

В этом примере Oracle оператора LIKE, мы ищем для всех customers , чьи last_name содержит символы 'er'.

Вот еще более сложный пример, использования экранирующего символа в операторе LIKE.

Этот пример Oracle оператора LIKE возвращает всех suppliers , имена которых начинаются с H и заканчиваются на %. Например, это может вернуть значение, такое как 'Hello%'.

Описание

SQL условие LIKE позволяет использовать подстановочные символы для сопоставления с шаблоном в запросе. Условие LIKE используется в предложении WHERE оператора SELECT, INSERT, UPDATE или DELETE.

Пример - использование оператора NOT с условием LIKE

Далее давайте рассмотрим пример использования оператора NOT с условием LIKE.
В этом примере у нас есть таблица suppliers со следующими данными:

supplier_id supplier_name city state
100 Yandex Moscow Russian
200 Google Lansing Michigan
300 Oracle Redwood City California
400 Bing Redmond Washington
500 Yahoo Sunnyvale Washington
600 DuckDuckGo Paoli Pennsylvania
700 Qwant Paris France
800 Facebook Menlo Park California
900 Electronic Arts San Francisco California

Давайте посмотрим на все записи в таблице suppliers , где supplier_name не содержит литеру 'o'. Введите следующий SQL оператор.

Будут выбраны 4 записи. Вот результаты, которые вы должны получить.

supplier_id supplier_name city state
100 Yandex Moscow Russian
300 Oracle Redwood City California
400 Bing Redmond Washington
700 Qwant Paris France

В этом примере в таблице suppliers есть четыре записи, в которых supplier_name не содержит литеру 'o'.

Значение результата

Оператор LIKE возвращает значение TRUE, если аргумент match_expression совпадает с указанным аргументом pattern.

Часто задаваемые вопросы

Вопрос: Как объединить Oracle функцию UPPER с Oracle оператором LIKE? Я пытаюсь запросить все записи текстового поля, которое содержат слово "test". Проблема заключается в том, что поле может иметь следующие значения: TEST, Test или test.

Ответ: Для того чтобы ответить на этот вопрос, давайте рассмотрим пример.

Давайте предположим, что у нас есть таблица suppliers с полем под названием supplier_name , которое содержит значения TEST, Test или test.

Если мы хотим найти все записи, содержащие слово "test", независимо от того в каком виде они сохранены как TEST, Test или test, то мы могли бы выполнить одно из следующих предложений SELECT:

В этом учебном материале вы узнаете, как использовать SQL условие LIKE (для сопоставления с шаблоном) с синтаксисом и примерами.

Пример - использование escape-символов с условием LIKE

test_id test_value
1 10%
2 25%
3 100
4 99

Мы могли бы вернуть все записи из таблицы test , где test_value содержит литерал % . Введите следующий SQL оператор.

Определяет, совпадает ли указанная символьная строка с заданным шаблоном. Шаблон может включать обычные символы и символы-шаблоны. Во время сравнения с шаблоном необходимо, чтобы его обычные символы в точности совпадали с символами, указанными в строке. Символы-шаблоны могут совпадать с произвольными элементами символьной строки. Использование символов-шаблонов в отличие от использования операторов сравнения строки (= и !=) делает оператор LIKE более гибким. Если тип данных одного из аргументов не является символьной строкой, компонент Компонент SQL Server Database Engine, если это возможно, преобразует его в тип данных символьной строки.

Использование символов-шаблонов в качестве литералов

Символы-шаблоны могут быть использованы в качестве литералов. Чтобы использовать символ-шаблон в качестве литерала, его необходимо заключать в скобки. В следующей таблице представлены несколько примеров применения ключевого слова LIKE вместе с символами-шаблонами [ ].

Символ Значение
LIKE '5[%]' 5 %
LIKE '[_]n' _n
LIKE '[a-cdf]' a, b, c, d или f
LIKE '[-acdf]' -, a, b, c, d или f
LIKE '[ [ ]' [
LIKE ']' ]
LIKE 'abc[_]d%' abc_d и abc_de
LIKE 'abc[def]' abcd, abce и abcf

Пример - использование подстановочного символа _ в условии LIKE

Далее рассмотрим, как подстановочный символ _ (символ подчеркивания) работает в условии LIKE. Помните, что подстановочный символ _ ищет ровно один символ, в отличие от подстановочного символа % .
Используя таблицу categories со следующими данными:

category_id category_name
25 Deli
50 Produce
75 Bakery
100 General Merchandise
125 Technology

Попробуем найти все записи из таблицы categories , где category_id имеет длину 2 цифры и заканчивается на '5'. Введите следующий SQL оператор.

Будет выбрано 2 записи. Вот результаты, которые вы должны получить.

category_id category_name
25 Deli
75 Bakery

В этом примере есть 2 записи, которые будут соответствовать шаблону - category_id со значениями 25 и 75. Обратите внимание, что category_id равный 125 не был выбран потому что символ _ соответствует только одному символу.

Использование нескольких подстановочных символов _ в условии LIKE

Если вы хотите сопоставить трехзначное значение, заканчивающееся на «5», вам нужно будет использовать подстановочный символ _ два раза. Вы можете изменить свой запрос следующим образом.

Теперь вы вернете значение category_id равное 125.

category_id category_name
125 Technology

Параметры или аргументы

Символьное выражение, такие как поле или столбец.

Символьное выражение, которое содержит сопоставление с pattern . pattern , которые вы можете выбрать:

подстановочный
символ		 пояснение
% Соответствует любой строке любой длины (в том числе нулевой длины)
_ Соответствует одному символу

escape_character
Необязательный. Позволяет проверять наличие литералов подстановочных символов, таких как % или _ .

Синтаксис

Синтаксис для условия LIKE в SQL.

Параметры или аргумент

expression Символьное выражение, такое как поле или столбец pattern Символьное выражение, которое содержит сопоставление с шаблоном. Подстановочные символы, которые вы можете выбрать:

Символ Объяснение
% Позволяет сопоставить любую строку любой длины (включая нулевую длину)
_ Позволяет сопоставить одиночный символ
ESCAPE 'escapecharacter' Необязательный. Это позволяет вам сопоставлять шаблоны с литеральными подстановочными символами, такими как % или _ .

Использование символа-шаблона «%»

Если в операторе LIKE указать символ '5%', то компонент Компонент Database Engine будет искать число «5», за которым следует любая строка с числом символов от нуля и больше.

Например, при выполнении следующего примера отображаются все динамические административные представления базы данных AdventureWorks2012, так как все они начинаются символами dm .

Чтобы отобразить все объекты, не являющиеся динамическими административными представлениями, используется синтаксис NOT LIKE 'dm%' . Например, если всего имеется 32 объекта и оператор LIKE выдает 13 наименований, совпадающих с шаблоном, то оператор NOT LIKE возвращает 19 объектов, не соответствующих указанному в операторе LIKE шаблону.

По такому шаблону, как LIKE '[^d][^m]%' , не всегда будут возвращаться одни и те же имена. Вместо 19 имен можно найти только 14, так как имена, которые начинаются с буквы d или у которых второй буквой является m , будут исключены из результата, как и имена динамических административных представлений. Причиной такой реакции на событие является поэтапный поиск отрицательных символов-шаблонов: за один шаг обрабатывается один символ-шаблон. Процесс поиска совпадений прекращается при возникновении сбоя на любой стадии выполнения.

Вы также можете использовать escape символ с символом _ в условии LIKE.

Этот пример LIKE возвращает всех suppliers , имена которых начинаются с 'H' и заканчиваются на '_'. Например, это может вернуть значение, такое как 'Hello_'.

Комментарии

При использовании оператора LIKE для сравнения строк во внимание принимаются все символы строки-шаблона. К значимым символам также относятся начальные и конечные пробелы. Если операция сравнения в запросе должна вернуть все строки, содержащие строки LIKE 'абв ' (с символом пробела на конце), то строка, содержащая "абв" (без пробела), не будет возвращена. Однако завершающие пробелы в выражении, с которым сравнивается шаблон, не учитываются. Если операция сравнения в запросе должна вернуть все строки, содержащие строки LIKE 'абв' (без знака пробела на конце), то будут возвращены все строки, содержащие «абв», как с завершающими пробелами, так и без них.

При сравнении строк с помощью оператора LIKE с использованием шаблона, содержащего тип данных char и varchar, могут возникнуть проблемы из-за методов хранения каждого типа данных. В ходе выполнения следующего примера локальная переменная char передается хранимой процедуре, а затем с помощью сравнения с шаблоном выполняется поиск всех сотрудников, чьи фамилии начинаются с указанной последовательности букв.

Выполнение процедуры FindEmployee не дает результатов, так как переменная типа char ( @EmpLName ) всегда имеет длину в 20 символов, до которой дополняется завершающими знаками пробела. Переменные, содержащиеся в столбце LastName , имеют тип varchar. Поэтому завершающие пробелы в них не дописываются. Данная процедура завершается неудачей, так как завершающие пробелы учитываются.

Процедура из следующего примера выполняется успешно, так как завершающие пробелы к переменной типа varchar не добавляются.

Пример - использование подстановочного символа % в условии LIKE

Давайте рассмотрим, как подстановочный символ % работает в SQL условии LIKE. Помните, что подстановочный символ % соответствует любой строке любой длины (включая нулевую длину).
В этом первом примере мы хотим найти все записи в таблице customers , где last_name клиента начинается с 'C'.
В этом примере у нас есть таблица customers со следующими данными:

Введите следующий SQL оператор.

Будет выбрано 2 записи. Вот результаты, которые вы должны получить.

В этом примере возвращаются записи таблицы customers , где last_name начинается с 'C'. Как видите, были возвращены записи по фамилиям Cruise и Crowe.
Поскольку условие LIKE не чувствительно к регистру, следующий SQL оператор вернет те же результаты.

Использование нескольких подстановочных символов % в условии LIKE

Вы также можете использовать подстановочный символ % несколько раз с условием LIKE.
Используя ту же таблицу customers со следующими данными:

Попробуем найти все значения last_name из таблицы customers , где last_name содержит букву 'e'.
Введите следующий SQL оператор:

Будет выбрано 3 записей. Вот результаты, которые вы должны получить.

last_name
Bieber
Gomez
Depp

В этом примере фамилии Bieber, Gomez и Depp содержат букву 'е'.

Пример использования _ (символ подчеркивание)

Далее, давайте рассмотрим, как подстановочный символ _ (символ подчеркивания) работает в Oracle операторе LIKE. Помните, что _ ищет только один символ.

В этом примере Oracle LIKE вернет всех suppliers , чье supplier_name имеет длину 5 символов, где первые два символа является 'Sm', а последние два символа это 'th'. Например, он может вернуть suppliers , чьи supplier_name являются 'Smith', 'Smyth', 'Smath' или 'Smeth' и т.д.

Вот еще один пример:

Ища номер счета, вы можете обнаружить, что у вас есть только 5 из 6 цифр. В приведенном выше примере, будет возвращено потенциально 10 последних записей (где отсутствующее значение может быть от 0 до 9). Например, запрос может вернуть suppliers , чьи account_number являются:

923140, 923141, 923142, 923143, 923144, 923145, 923146, 923147, 923148, 923149

Совпадение с шаблоном с использованием оператора LIKE

Оператор LIKE поддерживает шаблоны в ASCII и Юникоде. Если все аргументы (match_expression, pattern и escape_character, если он указан) имеют символьный тип ASCII, то применяется шаблон ASCII. В случае, когда какой-либо из аргументов имеет тип данных Юникод, выполняется преобразование всех аргументов в Юникод и применяется шаблон Юникод. Если вы используете оператор LIKE с типом данных Юникода (nchar или nvarchar), завершающие пробелы учитываются в отличие от других типов данных (не Юникода). Работа оператора LIKE с данными в Юникоде совместима со стандартом ISO. Принцип работы оператора LIKE с данными ASCII совместим с более ранними версиями SQL Server.

Приведенные ниже примеры поясняют различия между результатами сравнения данных с шаблонами оператора LIKE, представленными в Юникоде и ASCII.

Операции сравнения с помощью оператора LIKE зависят от параметров сортировки. Дополнительные сведения см. в разделе COLLATE (Transact-SQL).

Примечание

Смотрите также Oracle REGEXP_LIKE.

Типы результата

Boolean

Синтаксис

ESCAPE и STRING_ESCAPE сейчас не поддерживаются в Azure Synapse Analytics и Система платформы аналитики (PDW).

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Пример использования оператора NOT

Далее, давайте рассмотрим то, как вы будете использовать Oracle оператор NOT с подстановочными символами.

Давайте использовать % с оператором NOT. Кроме того, можно использовать Oracle оператор LIKE для поиска suppliers (поставщиков), имена которых не начинаются на 'W'.

Размещая оператор NOT перед LIKE, вы можете получить всех suppliers , чьи supplier_name не начинаются на 'W'.

Синтаксис

Синтаксис LIKE в Oracle/PLSQL:

Читайте также: