PATINDEX: примеры (SQL)
PATINDEX('%pattern%', expression): intОписание функции PATINDEX
Функция PATINDEX в MS SQL Server выполняет поиск первого вхождения заданного шаблона в строке выражение и возвращает начальную позицию этого вхождения. Ее применяют для определения позиции подстроки, соответствующей шаблону с использованием символов подстановки.
Функция используется в сценариях, требующих поиска строк по сложным шаблонам, например, для валидации форматов данных, извлечения фрагментов текста или поиска нестандартных комбинаций символов.
Синтаксис функции:
PATINDEX ( '%pattern%' , expression )
Аргументы функции:
- pattern – строка, содержащая шаблон для поиска. Может включать символы подстановки: % (любое количество символов) и _ (ровно один символ). Шаблон обязательно должен быть заключен в знаки процента, если требуется поиск внутри строки. Поиск чувствителен к регистру, если не указано иное.
- expression – строковое выражение, обычно имя столбца или переменной, в котором выполняется поиск. Может иметь тип данных char, varchar, nchar, nvarchar, text или ntext.
Возвращаемые значения:
- Функция возвращает целое число – позицию первого символа найденного шаблона. Нумерация начинается с 1.
- Если шаблон не найден, возвращается 0.
- Если любой из аргументов равен NULL, функция возвращает NULL.
Примеры применения PATINDEX
Пример с поиском простого шаблона:
SELECT PATINDEX('%soft%', 'Microsoft SQL Server') AS Position;Position 6
Поиск с использованием символа подстановки:
SELECT PATINDEX('%S_rv_r%', 'Microsoft SQL Server') AS Position;Position 13
Поиск с начала строки:
SELECT PATINDEX('Mic%', 'Microsoft SQL Server') AS Position;Position 1
Пример, когда шаблон не найден:
SELECT PATINDEX('%Oracle%', 'Microsoft SQL Server') AS Position;Position 0
Работа с переменными:
DECLARE @text NVARCHAR(100) = 'Телефон: +7-900-123-45-67';
SELECT PATINDEX('%+7-[0-9][0-9][0-9]-%', @text) AS PhonePosition;PhonePosition 10
Похожие функции в MS SQL
CHARINDEX – функция для поиска подстроки без использования шаблонов. Работает быстрее PATINDEX, но не поддерживает символы подстановки. Предпочтительна для поиска фиксированных строк.
LIKE – оператор, используемый в условиях WHERE для сопоставления с шаблоном. PATINDEX и LIKE используют одинаковые шаблоны, но PATINDEX возвращает позицию, а LIKE – логическое значение.
STRING_SPLIT (с SQL Server 2016) – функция для разделения строки по разделителю. Может использоваться совместно с PATINDEX для сложных операций извлечения данных.
Типичные ошибки
Забывают заключить шаблон в знаки процента:
SELECT PATINDEX('soft', 'Microsoft SQL Server') AS Position;Position 0
Не учитывается чувствительность к регистру:
-- При чувствительной к регистру сортировке
SELECT PATINDEX('%sql%', 'Microsoft SQL Server') AS Position;Position 0
Использование недопустимых символов подстановки:
SELECT PATINDEX('%S*r%', 'Microsoft SQL Server') AS Position;Position 0
Обработка NULL-значений:
SELECT PATINDEX('%test%', NULL) AS Position;Position NULL
Изменения в последних версиях
Начиная с SQL Server 2012, функция PATINDEX поддерживает работу с типами данных varchar(max) и nvarchar(max). В более ранних версиях были ограничения на длину текста.
В SQL Server 2017 добавлена возможность использовать функцию с UTF-8 в collation, что улучшило обработку многобайтовых символов.
Расширенные примеры PATINDEX
Извлечение домена из email:
DECLARE @email VARCHAR(100) = 'user@example.com';
SELECT SUBSTRING(@email, PATINDEX('%@%', @email) + 1, LEN(@email)) AS Domain;Domain example.com
Поиск первого нецифрового символа:
SELECT PATINDEX('%[^0-9]%', '123ABC456') AS FirstNonDigit;FirstNonDigit 4
Поиск позиции слова, состоящего из ровно 3 букв:
SELECT PATINDEX('% [a-zA-Z][a-zA-Z][a-zA-Z] %', 'This is a SQL test') AS ThreeLetterWordPos;ThreeLetterWordPos 6
Валидация формата телефонного номера:
SELECT
CASE
WHEN PATINDEX('+7-[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]', '+7-912-345-67-89') = 1
THEN 'Valid'
ELSE 'Invalid'
END AS PhoneCheck;PhoneCheck Valid
Нахождение позиции второго вхождения шаблона:
DECLARE @str NVARCHAR(100) = 'ab ab ab';
DECLARE @pattern NVARCHAR(100) = '%ab%';
SELECT PATINDEX(@pattern, STUFF(@str, 1, PATINDEX(@pattern, @str) + 1, '')) +
PATINDEX(@pattern, @str) AS SecondPosition;SecondPosition 4
Аналоги функции в других СУБД
MySQL: функция LOCATE или оператор REGEXP_INSTR для регулярных выражений.
SELECT REGEXP_INSTR('Microsoft SQL Server', 'S.rv.r');13
Oracle: функции INSTR или REGEXP_INSTR.
SELECT REGEXP_INSTR('Microsoft SQL Server', 'S.rv.r') FROM dual;13
PostgreSQL: функция POSITION для простого поиска или SUBSTRING с регулярными выражениями.
SELECT POSITION('soft' IN 'Microsoft SQL Server');6
SQLite: отсутствует прямая замена, но можно использовать функцию INSTR для простого поиска.
SELECT INSTR('Microsoft SQL Server', 'soft');6