Sql escape символы
В предикате 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 | Lansing | Michigan | |
| 300 | Oracle | Redwood City | California |
| 400 | Bing | Redmond | Washington |
| 500 | Yahoo | Sunnyvale | Washington |
| 600 | DuckDuckGo | Paoli | Pennsylvania |
| 700 | Qwant | Paris | France |
| 800 | 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 Символьное выражение, которое содержит сопоставление с шаблоном. Подстановочные символы, которые вы можете выбрать:
| Символ | Объяснение |
|---|---|
| % | Позволяет сопоставить любую строку любой длины (включая нулевую длину) |
| _ | Позволяет сопоставить одиночный символ |
Использование символа-шаблона «%»
Если в операторе 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:
Читайте также: