POSITION и STRPOS в SQL: как найти подстроку внутри строки

Иногда в SQL нужно не просто вывести строку, а понять, где внутри неё находится нужный кусок.

Например:

• где в email стоит символ @;
• есть ли в артикуле дефис;
• с какой позиции начинается слово в названии;
• где заканчивается префикс в коде вроде EU-10045;
• можно ли разрезать строку на две части по разделителю.

Для таких задач в PostgreSQL есть две функции:

POSITION(substring IN string)
STRPOS(string, substring)

Обе делают одно и то же: ищут подстроку внутри строки и возвращают позицию первого вхождения.

Проще говоря, они отвечают на вопрос:

«С какого символа начинается нужный фрагмент?»

Разберём на понятных примерах.

Простая идея: найти позицию символа

Допустим, у нас есть email:

anna@example.com

Мы хотим понять, где находится символ @.

SELECT POSITION('@' IN 'anna@example.com') AS at_position;

Результат:

at_position
-----------
5

Почему 5?

Потому что PostgreSQL считает символы так:

a n n a @ e x a m p l e . c o m
1 2 3 4 5 6 7 8 9 ...

Символ @ стоит на пятой позиции.

Главное отличие от многих языков программирования: в SQL позиция начинается с 1, а не с 0.

POSITION и STRPOS: две формы одной операции

В PostgreSQL есть два популярных варианта поиска подстроки.

Первый — стандартный SQL-синтаксис:

SELECT POSITION('@' IN email) FROM users;

Он читается почти как английская фраза: «позиция @ в email».

Второй — короткая PostgreSQL-функция:

SELECT STRPOS(email, '@') FROM users;

Здесь сначала идёт строка, в которой ищем, а потом то, что ищем.

Пример:

SELECT
  email,
  POSITION('@' IN email) AS pos_by_position,
  STRPOS(email, '@')     AS pos_by_strpos
FROM users;

Обе колонки вернут одинаковое значение.

Например:

email            | pos_by_position | pos_by_strpos
-----------------+-----------------+--------------
anna@example.com | 5               | 5
bob@test.io      | 4               | 4

Что выбрать?

Если хотите более переносимый SQL — используйте POSITION.

Если пишете под PostgreSQL и хотите короткую запись — можно использовать STRPOS.

Что вернётся, если подстроки нет

Если искомого фрагмента в строке нет, PostgreSQL возвращает 0.

SELECT STRPOS('hello world', '@') AS result;

Результат:

result
------
0

Это важный момент.

В PostgreSQL:

• 1, 2, 3 и дальше — подстрока найдена;
• 0 — подстрока не найдена;
• NULL — одна из входных строк была NULL.

Это удобно использовать в WHERE.

Например, найти пользователей с некорректными email, где вообще нет @:

SELECT id, email
FROM users
WHERE STRPOS(email, '@') = 0;

А так можно взять только строки, где @ есть:

SELECT id, email
FROM users
WHERE POSITION('@' IN email) > 0;

Условие > 0 означает: «символ найден где-то внутри строки».

NULL на входе

Если строка равна NULL, результат тоже будет NULL.

SELECT STRPOS(NULL, '@') AS result;

Результат:

result
------
NULL

Это обычное поведение SQL: если значение неизвестно, результат операции тоже становится неизвестным.

Из-за этого есть маленькая ловушка.

Допустим, в таблице users колонка email может быть NULL.

SELECT id, email
FROM users
WHERE STRPOS(email, '@') = 0;

Такой запрос найдёт строки, где email есть, но в нём нет @.

Но строки с email = NULL он не вернёт, потому что STRPOS(NULL, '@') даёт NULL, а не 0.

Если вы хотите считать NULL как пустую строку, используйте COALESCE:

SELECT id, email
FROM users
WHERE STRPOS(COALESCE(email, ''), '@') = 0;

Теперь NULL будет временно превращаться в пустую строку, и запрос тоже попадёт в условие.

Пример: проверить email на наличие @

Допустим, в таблице users есть такие данные:

id | email
---+------------------
1  | anna@example.com
2  | bob.test.io
3  | manager@shop.com
4  | NULL

Найдём строки, где символ @ есть:

SELECT id, email
FROM users
WHERE POSITION('@' IN email) > 0;

Результат:

id | email
---+------------------
1  | anna@example.com
3  | manager@shop.com

А теперь найдём строки, где @ нет или email вообще не заполнен:

SELECT id, email
FROM users
WHERE STRPOS(COALESCE(email, ''), '@') = 0;

Результат:

id | email
---+------------
2  | bob.test.io
4  | NULL

Важно: это не полноценная проверка email. Мы просто проверили наличие символа @. Настоящая валидация email сложнее. Но для поиска явно грязных данных такой приём полезен.

Связка с SUBSTRING: разрезаем строку по позиции

Сама по себе позиция нужна не так часто. Обычно её используют как границу для SUBSTRING.

Например, у нас есть email:

anna@example.com

Мы хотим получить две части:

anna
example.com

То есть:

• всё до @;
• всё после @.

Для начала посмотрим позицию @:

SELECT POSITION('@' IN 'anna@example.com') AS at_position;

Результат:

at_position
-----------
5

Значит:

• локальная часть начинается с 1-го символа и идёт до 4-го;
• домен начинается с 6-го символа.

Запишем это в SQL:

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;

Результат:

email            | local_part | domain
-----------------+------------+------------
anna@example.com | anna       | example.com
bob@test.io      | bob        | test.io

Разберём выражения.

SELECT SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) FROM users;

Здесь мы говорим:

«Возьми строку с первого символа и забери столько символов, сколько идёт до @.»

Если @ стоит на 5-й позиции, то POSITION(...) - 1 даст 4.

Значит, мы получим первые 4 символа: anna.

Вторая часть:

SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1) FROM users;

Здесь мы говорим:

«Начни сразу после @ и возьми всё до конца строки.»

Если @ стоит на 5-й позиции, то POSITION(...) + 1 даст 6.

Значит, домен начнётся с 6-го символа: example.com.

Почему важно проверять POSITION > 0

Посмотрите на этот запрос:

SELECT
  email,
  SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) AS local_part
FROM users;

Если в email нет @, функция POSITION вернёт 0.

Тогда выражение станет таким:

SELECT SUBSTRING(email FROM 1 FOR -1) FROM users;

Это уже выглядит странно. Результат может оказаться не тем, которого вы ожидаете.

Поэтому перед разрезанием строки лучше отфильтровать строки, где разделитель действительно есть:

SELECT email FROM users
WHERE POSITION('@' IN email) > 0;

Полный вариант:

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;

Это простое правило сильно снижает количество странных ошибок.

Более аккуратный вариант через CTE

В примере выше POSITION('@' IN email) повторяется несколько раз.

Для учебного примера это нормально, но в реальном запросе лучше сделать его один раз и дать понятное имя.

Например:

WITH users_with_position AS (
  SELECT
    id,
    email,
    POSITION('@' IN email) AS at_pos
  FROM users
)
SELECT
  id,
  email,
  SUBSTRING(email FROM 1 FOR at_pos - 1) AS local_part,
  SUBSTRING(email FROM at_pos + 1)       AS domain
FROM users_with_position
WHERE at_pos > 0;

Так запрос читать проще:

1. сначала нашли позицию @;
2. потом использовали её для разрезания строки;
3. строки без @ отфильтровали.

Для новичка это особенно полезно: меньше вложенности, меньше шансов потеряться в скобках.

Пример: посчитать заказы по доменам email

Допустим, у нас есть таблицы:

users
-----
id
email

orders
------
id
user_id
amount
created_at

Мы хотим понять, с каких email-доменов приходит больше заказов.

Например:

gmail.com
yandex.ru
company.com

Запрос может выглядеть так:

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

Здесь мы сначала получаем домен из email, а потом группируем заказы по этому домену.

Результат может быть таким:

domain      | orders_count
------------+-------------
gmail.com   | 120
yandex.ru   | 87
company.com | 34

Такой запрос уже похож на реальную аналитическую задачу: не просто «поиграться со строкой», а подготовить данные для отчёта.

Когда лучше использовать SPLIT_PART

Для email есть более короткий способ — split_part.

Если строку нужно разрезать по разделителю, а потом взять конкретную часть, split_part часто читается лучше, чем связка POSITION + SUBSTRING.

Например, домен email:

SELECT split_part(email, '@', 2) AS domain
FROM users;

Локальная часть:

SELECT split_part(email, '@', 1) AS local_part
FROM users;

Это проще, чем вручную считать позицию @.

Тогда зачем вообще нужны POSITION и STRPOS?

Они полезны, когда вам нужна именно позиция:

• проверить, есть ли разделитель;
• понять, где он стоит;
• использовать позицию в более сложной логике;
• найти начало слова или фрагмента;
• обрезать строку не только по разделителю, но и по вычисленной границе.

Пример:

SELECT id, sku
FROM products
WHERE POSITION('-' IN sku) > 0;

Так мы ищем товары, у которых в артикуле есть дефис.

Пример: разобрать артикул товара

Допустим, артикул товара выглядит так:

EU-10045
US-20077
KZ-30012

Первые две буквы — регион, после дефиса — номер товара.

Можно разрезать такой код через POSITION:

WITH products_with_dash AS (
  SELECT
    id,
    sku,
    POSITION('-' IN sku) AS dash_pos
  FROM products
)
SELECT
  id,
  sku,
  SUBSTRING(sku FROM 1 FOR dash_pos - 1) AS region,
  SUBSTRING(sku FROM dash_pos + 1)       AS product_number
FROM products_with_dash
WHERE dash_pos > 0;

Результат:

id | sku      | region | product_number
---+----------+--------+---------------
1  | EU-10045 | EU     | 10045
2  | US-20077 | US     | 20077
3  | KZ-30012 | KZ     | 30012

Да, для такого случая тоже можно использовать split_part:

SELECT
  sku,
  split_part(sku, '-', 1) AS region,
  split_part(sku, '-', 2) AS product_number
FROM products;

Но пример с POSITION хорошо показывает сам принцип: функция находит границу, а SUBSTRING по этой границе отрезает нужную часть.

POSITION находит только первое вхождение

POSITION и STRPOS ищут слева направо и возвращают только первое вхождение.

SELECT POSITION('@' IN 'a@b@c') AS first_at;

Результат:

first_at
--------
2

В строке есть две @, но функция вернула позицию первой.

Если вам нужно просто проверить, что символ есть, этого достаточно.

Но если нужно найти второе, третье или последнее вхождение, задача становится сложнее.

Например, второе вхождение можно искать в хвосте строки:

WITH source AS (
  SELECT 'a@b@c'::text AS value
),
first_search AS (
  SELECT
    value,
    STRPOS(value, '@') AS first_pos
  FROM source
),
second_search AS (
  SELECT
    value,
    first_pos,
    STRPOS(SUBSTRING(value FROM first_pos + 1), '@') AS second_pos_in_tail
  FROM first_search
)
SELECT
  value,
  first_pos,
  CASE
    WHEN first_pos > 0 AND second_pos_in_tail > 0
      THEN first_pos + second_pos_in_tail
    ELSE 0
  END AS second_pos
FROM second_search;

Результат:

value | first_pos | second_pos
------+-----------+-----------
a@b@c | 2         | 4

Но честно: если вы дошли до такого кода, возможно, стоит посмотреть в сторону регулярных выражений или специальных функций для работы с массивами и разделителями.

Для простых задач POSITION хорош. Для сложного парсинга строк лучше не превращать SQL в головоломку.

Поиск чувствителен к регистру

POSITION и STRPOS различают большие и маленькие буквы.

SELECT POSITION('A' IN 'banana') AS result;

Результат:

result
------
0

Почему 0?

Потому что в строке banana есть маленькая a, но нет большой A.

Если нужен поиск без учёта регистра, можно привести обе строки к одному регистру:

SELECT POSITION(LOWER('A') IN LOWER('banana')) AS result;

Или проще:

SELECT POSITION('a' IN LOWER('banana')) AS result;

Результат:

result
------
2

На таблице это может выглядеть так:

SELECT id, name
FROM products
WHERE POSITION('pro' IN LOWER(name)) > 0;

Такой запрос найдёт и Pro Keyboard, и PRO Mouse, и basic pro cable.

Но помните про индексы: функции вроде LOWER(name) в WHERE могут мешать обычному индексу по name. Для частого поиска по тексту лучше отдельно разбираться с индексами, ILIKE, полнотекстовым поиском или trigram-индексами.

Пустая искомая строка

Есть ещё один неожиданный случай:

SELECT POSITION('' IN 'abc') AS result;

В PostgreSQL результат будет:

result
------
1

Пустая строка считается найденной в начале любой строки.

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

Например:

SELECT title FROM articles
WHERE search_text <> ''
  AND POSITION(search_text IN title) > 0;

Иначе можно случайно получить ситуацию, где пользователь ничего не ввёл, а условие «найдено» сработало для всех строк.

Кириллица и многобайтные символы

В PostgreSQL POSITION и STRPOS работают с символами строки, а не с байтами.

Это значит, что кириллица считается нормально:

SELECT POSITION('@' IN 'анна@example.com') AS at_position;

Результат:

at_position
-----------
5

Счёт идёт по символам:

а н н а @ e x a m p l e . c o m
1 2 3 4 5

Для обычной работы с русским текстом это важно: позиция не «съезжает» из-за того, что кириллические символы в UTF-8 занимают больше одного байта.

MySQL: LOCATE, INSTR и POSITION

В MySQL тоже есть POSITION:

SELECT POSITION('@' IN email) AS at_pos
FROM users;

Но на практике в MySQL часто используют LOCATE или INSTR.

Главное — не перепутать порядок аргументов.

SELECT
  LOCATE('@', email) AS by_locate,
  INSTR(email, '@')  AS by_instr
FROM users;

У LOCATE порядок такой:

LOCATE(needle, haystack)

У INSTR порядок такой:

INSTR(haystack, needle)

То есть:

SELECT LOCATE('@', email), INSTR(email, '@') FROM users;

делают одно и то же, но аргументы стоят в разном порядке.

Это частая причина ошибок при переходе между PostgreSQL и MySQL.

Хорошая новость: результат тоже начинается с 1, а если подстрока не найдена, возвращается 0.

Как в MySQL искать не с начала строки

У MySQL есть удобная возможность: LOCATE принимает третий аргумент — позицию, с которой начинать поиск.

Например:

SELECT LOCATE('@', 'a@b@c', 3) AS second_at;

Результат:

second_at
---------
4

Здесь мы сказали: ищи @ в строке 'a@b@c', но начинай с третьего символа.

Первый @ стоит на позиции 2, поэтому он пропущен. Второй найден на позиции 4.

В PostgreSQL у POSITION и STRPOS такого третьего аргумента нет. Если нужно искать дальше первого вхождения, приходится искать в хвосте строки или использовать другие инструменты.

ClickHouse: position

В ClickHouse похожая функция называется position.

Порядок аргументов такой:

position(string, substring)

Например:

SELECT position('anna@example.com', '@') AS at_pos;

Результат:

at_pos
------
5

То есть порядок похож на PostgreSQL-функцию STRPOS, а не на стандартный синтаксис POSITION(substring IN string).

Для поиска без учёта регистра в ClickHouse есть отдельные функции, например positionCaseInsensitive.

Частые ошибки новичков

Первая ошибка — забыть, что счёт начинается с 1.

SELECT POSITION('@' IN 'a@b.com');

Результат будет 2, а не 1.

Вторая ошибка — ждать -1, если подстрока не найдена.

В PostgreSQL будет 0:

SELECT STRPOS('abc', 'x');

Результат:

0

Третья ошибка — не отфильтровать строки без разделителя перед SUBSTRING.

Если POSITION вернул 0, дальнейшая арифметика вроде POSITION(...) - 1 может дать странный результат.

Четвёртая ошибка — перепутать аргументы в разных функциях:

-- PostgreSQL
SELECT STRPOS(email, '@') FROM users;
-- MySQL
SELECT LOCATE('@', email), INSTR(email, '@') FROM users;
-- ClickHouse
SELECT position(email, '@') FROM users;

Пятая ошибка — использовать POSITION там, где проще и понятнее split_part.

Если нужно просто взять домен email, лучше так:

SELECT split_part(email, '@', 2) AS domain
FROM users;

А если нужна именно позиция символа — тогда POSITION или STRPOS.

Коротко

POSITION и STRPOS помогают найти, где внутри строки находится нужный фрагмент.

В PostgreSQL можно писать так:

SELECT POSITION('@' IN email) AS at_pos
FROM users;

Или так:

SELECT STRPOS(email, '@') AS at_pos
FROM users;

Обе функции возвращают позицию первого вхождения.

Важно запомнить:

• счёт начинается с 1;
• если подстрока не найдена, результат 0;
• если входное значение NULL, результат тоже NULL;
• поиск чувствителен к регистру;
• пустая искомая строка считается найденной на позиции 1;
• POSITION и STRPOS находят только первое вхождение.

Чаще всего эти функции используют в двух сценариях.

Первый — проверить, есть ли подстрока:

SELECT id, email
FROM users
WHERE POSITION('@' IN email) > 0;

Второй — найти границу и разрезать строку через SUBSTRING:

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;

Если же строку нужно просто разделить по известному разделителю, часто лучше использовать split_part:

SELECT split_part(email, '@', 2) AS domain
FROM users;

Главная мысль такая: POSITION и STRPOS — это не магический парсер строк, а простой инструмент для поиска позиции. Но если хорошо понимать 1, 0, NULL и связку с SUBSTRING, с их помощью можно уверенно разбирать коды, email, артикулы и другие строковые данные прямо в SQL.