POSITION and STRPOS in SQL: Finding a Substring's Index

Когда нужно понять, где внутри строки сидит подстрока — например, символ @ в email — в SQL это делают функции POSITION и STRPOS. Обе возвращают индекс первого вхождения, и вместе с SUBSTRING это базовый инструмент для разбора строк прямо в запросе.

Обе функции отвечают на один вопрос: с какого символа в строке начинается первое вхождение искомого фрагмента. Это нужно, когда email надо разрезать по @, артикул — по дефису, а составной ключ вроде region-id — по разделителю. Результат POSITION и STRPOS — это целое число, которое почти всегда идёт дальше в SUBSTRING как граница среза или в WHERE как признак «разделитель есть / его нет». Поэтому разбирать их стоит в паре с этими двумя сценариями, а не как отдельную функцию.

Две формы одной операции

В PostgreSQL есть два способа записать поиск подстроки, и они эквивалентны по результату:

• POSITION(sub IN str) — стандарт SQL, читается почти как фраза «позиция sub в str»;
• STRPOS(str, sub) — короткая функция PostgreSQL, где сначала идёт строка, потом искомое.

-- Both return the index of '@' inside the email
SELECT
  email,
  POSITION('@' IN email) AS pos_standard,
  STRPOS(email, '@')     AS pos_shorthand
FROM users;

Главная ловушка для тех, кто пришёл из языков программирования: индекс начинается с 1, а не с 0. То есть для 'a@b.com' обе функции вернут 2. А если подстроки нет вообще, результат — 0 (а не -1 и не NULL), что удобно для условий в WHERE.

1-based и значение 0

Поскольку 0 означает «не найдено», его легко использовать как фильтр:

-- Users whose email has no '@' at all (likely bad data)
SELECT id, email
FROM users
WHERE STRPOS(email, '@') = 0;

-- Users on a corporate domain: '@' is present
SELECT id, email
FROM users
WHERE POSITION('@' IN email) > 0;

Ловушка: POSITION находит только первое вхождение и ищет слева направо. Для строки вроде 'a@b@c' вы получите позицию первой @ (значение 2), а вторую так не найти. Если str или sub равны NULL, результат тоже NULL — это типичная причина «пропавших» строк, поэтому оборачивайте поле в COALESCE(email, ''), когда колонка допускает NULL.

Связка с SUBSTRING: режем строку

Сама по себе позиция нужна редко — обычно её сразу скармливают в SUBSTRING, чтобы вытащить часть строки. Классика: разбить email на локальную часть и домен по @.

-- Split email into local part and domain
SELECT
  email,
  SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1)        AS local_part,
  SUBSTRING(email FROM POSITION('@' IN email) + 1)              AS domain
FROM users
WHERE POSITION('@' IN email) > 0;

Логика простая: POSITION(...) - 1 — длина части до @, а POSITION(...) + 1 — старт сразу после @. Условие > 0 в WHERE страхует от строк без @, иначе - 1 дала бы пустую или странную выборку.

Тот же приём работает для агрегатов — например, посчитать заказы по доменам пользователей:

-- Order counts grouped by email domain
SELECT
  SUBSTRING(u.email FROM POSITION('@' IN u.email) + 1) AS domain,
  COUNT(o.id) AS orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE POSITION('@' IN u.email) > 0
GROUP BY domain
ORDER BY orders DESC;

MySQL: LOCATE и INSTR

В MySQL POSITION(sub IN str) тоже работает (это стандарт), но чаще пишут LOCATE или INSTR. Обратите внимание на разный порядок аргументов:

-- MySQL: same 1-based result, 0 when not found
SELECT
  LOCATE('@', email) AS by_locate,   -- needle first, then haystack
  INSTR(email, '@')  AS by_instr;    -- haystack first, then needle

Ключевые отличия от PostgreSQL:

• у LOCATE(sub, str) сначала идёт искомое, у INSTR(str, sub) — наоборот; легко перепутать;
• LOCATE принимает третий аргумент — позицию старта поиска: LOCATE('@', email, 3) ищет с третьего символа, что позволяет искать второе вхождение;
• в ClickHouse функция называется position(str, sub) (порядок как у STRPOS), плюс есть positionCaseInsensitive для поиска без учёта регистра.

Ещё одна полезная мелочь: обе функции работают и по строковым литералам, и по выражениям, поэтому позицию можно искать в результате CONCAT, LOWER или любого другого преобразования — например, нормализовать регистр перед поиском через POSITION('@' IN LOWER(email)).

У POSITION и STRPOS есть три значения, на которых легко обжечься. Пустая искомая строка POSITION('' IN email) в PostgreSQL возвращает 1, а не 0, поэтому условие «разделитель есть» лучше писать как > 0 и не подавать в него пустой sub. Поиск регистрозависимый, так что POSITION('A' IN email) не найдёт строчную a — для регистронезависимого поиска нормализуйте строку через LOWER. И помните про многобайтные символы: в PostgreSQL POSITION и STRPOS считают позицию в символах, а не в байтах, поэтому кириллица или эмодзи перед @ не сдвинут результат неожиданно — счёт остаётся в символах строки.

Есть и предел применимости: POSITION и STRPOS находят только первое вхождение и не умеют в шаблоны. Если нужно второе вхождение @, в MySQL поможет третий аргумент LOCATE('@', email, pos + 1), а в PostgreSQL придётся искать заново в хвосте через STRPOS(SUBSTRING(...), '@') либо переходить к регуляркам. Когда же разделителей много или их позиция плавающая, вместо ручной арифметики позиций берите SPLIT_PART(email, '@', 2) для домена или regexp_match — они короче и устойчивее к грязным данным, чем цепочка из POSITION и SUBSTRING.

Итог: в PostgreSQL берите STRPOS для краткости или POSITION ... IN для переносимости, всегда помните про индексацию с 1 и 0 при отсутствии, а в связке с SUBSTRING получаете полноценный парсер строк прямо в SQL.