CHARINDEX: примеры (SQL)
CHARINDEX(expressionToFind, expressionToSearch [, start_location]): intФункция CHARINDEX в MS SQL Server
Функция CHARINDEX выполняет поиск одного выражения символов внутри другого. При нахождении она возвращает начальную позицию искомого выражения. Если поиск не увенчался успехом, функция возвращает 0. Эта функция часто применяется в задачах текстового анализа, валидации данных, извлечения подстрок и условной обработки строковых полей.
Синтаксис функции включает три возможных аргумента:
- expression_to_find — выражение символьного типа, которое необходимо найти. Может быть типа CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, NTEXT, IMAGE.
- expression_to_search — выражение символьного типа, в котором осуществляется поиск. Поддерживает те же типы данных.
- start_location — необязательный целочисленный аргумент, задающий позицию в строке для начала поиска. Если значение не указано или равно 0, поиск начинается с начала строки.
Возвращаемое значение — целое число, обозначающее позицию первого вхождения. Нумерация символов начинается с 1. Если выражение не найдено, возвращается 0. Важно, что функция учитывает регистр символов, если для параметров сортировки установлена чувствительность к регистру.
Простые примеры использования
Поиск подстроки с начала строки.
SELECT CHARINDEX('мир', 'Привет, мир!') AS Position;Position -------- 9
Поиск с указанной начальной позиции.
SELECT CHARINDEX('а', 'барабан', 3) AS Position;Position -------- 5
Пример, когда подстрока не найдена.
SELECT CHARINDEX('солнце', 'Идёт дождь.') AS Position;Position -------- 0
Использование с переменными.
DECLARE @FindStr NVARCHAR(10) = 'SQL';
DECLARE @SearchStr NVARCHAR(100) = 'Изучаем MS SQL Server';
SELECT CHARINDEX(@FindStr, @SearchStr) AS Position;Position -------- 11
Аналогичные функции в T-SQL
PATINDEX — функция возвращает начальную позицию первого вхождения шаблона в строку. В отличие от CHARINDEX, поддерживает использование подстановочных знаков (например, '%'). PATINDEX('%н%', 'Имя') вернет 3.
STRING_SPLIT — функция разбивает строку на подстроки по указанному разделителю. Прямо не возвращает позицию, но полезна для разбора текста. Предпочтительнее использовать для деления строки на элементы.
SUBSTRING — функция извлекает подстроку из строки, начиная с указанной позиции. Часто используется в связке с CHARINDEX. Например, для извлечения текста от позиции найденного фрагмента.
Распространенные ошибки
1. Неучёт чувствительности к регистру. При разных параметрах сортировки результат может отличаться.
-- Если сортировка CI (case-insensitive)
SELECT CHARINDEX('A', 'abc') AS Pos; -- Вернет 1
-- Если сортировка CS (case-sensitive)
SELECT CHARINDEX('A', 'abc') AS Pos; -- Вернет 02. Неправильная интерпретация позиции. Позиция начала поиска должна быть положительным целым числом.
SELECT CHARINDEX('а', 'барабаны', 0) AS Pos; -- Начнет с начала, вернет 2
SELECT CHARINDEX('а', 'барабаны', -1) AS Pos; -- Вызовет ошибку3. Поиск пустой строки. Поиск пустой строки возвращает начальную позицию.
SELECT CHARINDEX('', 'тест') AS Pos; -- Вернет 14. Передача аргумента NULL. Если любой из аргументов NULL, результат NULL.
SELECT CHARINDEX(NULL, 'тест') AS Pos; -- Вернет NULLИзменения в последних версиях SQL Server
В SQL Server 2012 введена поддержка типов данных TEXT, NTEXT и IMAGE для аргументов функции, но рекомендуется использовать VARCHAR(MAX) и NVARCHAR(MAX). Основной синтаксис и логика работы остались неизменными на протяжении многих версий.
Расширенные примеры применения
Извлечение домена из адреса электронной почты.
DECLARE @Email VARCHAR(100) = 'user@example.com';
SELECT SUBSTRING(@Email, CHARINDEX('@', @Email) + 1, LEN(@Email)) AS Domain;Domain ------------ example.com
Поиск второго вхождения символа.
DECLARE @Str VARCHAR(20) = 'abracadabra';
SELECT CHARINDEX('a', @Str, CHARINDEX('a', @Str) + 1) AS SecondOccurrence;SecondOccurrence ---------------- 4
Разделение строки по разделителю.
DECLARE @Data VARCHAR(50) = 'Яблоко,Груша,Слива';
DECLARE @Delimiter CHAR(1) = ',';
SELECT
CHARINDEX(@Delimiter, @Data) AS FirstDelimPos,
LEFT(@Data, CHARINDEX(@Delimiter, @Data) - 1) AS FirstItem;FirstDelimPos FirstItem -------------- ---------- 7 Яблоко
Проверка наличия одного из нескольких слов.
DECLARE @Text NVARCHAR(200) = 'В лесу растут грибы и ягоды.';
SELECT
CASE
WHEN CHARINDEX('грибы', @Text) > 0 THEN 'Найдено грибы'
WHEN CHARINDEX('ягоды', @Text) > 0 THEN 'Найдено ягоды'
ELSE 'Не найдено'
END AS Result;Result -------------- Найдено грибы
Использование в условии WHERE для фильтрации.
-- Найти все записи, где в названии есть слово 'Книга'
SELECT ProductName FROM Products
WHERE CHARINDEX('Книга', ProductName) > 0;Аналоги функции в других СУБД и языках
MySQL: Функция LOCATE или INSTR. LOCATE('подстрока', 'строка', [позиция]) аналогична CHARINDEX.
SELECT INSTR('Привет, мир!', 'мир'); -- Возвращает 9Oracle: Функция INSTR. Синтаксис INSTR(строка, подстрока, [начало], [вхождение]).
SELECT INSTR('Привет, мир!', 'мир') FROM dual; -- Возвращает 9PostgreSQL: Функция STRPOS или POSITION. POSITION(подстрока IN строка).
SELECT POSITION('мир' IN 'Привет, мир!'); -- Возвращает 9SQLite: Функция INSTR. Синтаксис INSTR(строка, подстрока).
SELECT INSTR('Привет, мир!', 'мир'); -- Возвращает 9В большинстве случаев логика работы схожа, но могут быть отличия в обработке регистра и многобайтовых кодировок.