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

When you need to know where a substring sits inside a string — say, the @ in an email — SQL gives you POSITION and STRPOS. Both return the index of the first match, and paired with SUBSTRING they are the bread-and-butter tools for parsing strings right inside a query.

Two forms of one operation

PostgreSQL offers two ways to write a substring search, and they are equivalent in result:

• POSITION(sub IN str) — the SQL standard form, reading almost like the phrase "position of sub in str";
• STRPOS(str, sub) — the short PostgreSQL function, with the string first and the needle second.

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

The main trap for anyone coming from a programming language: the index starts at 1, not 0. So for 'a@b.com' both functions return 2. And when the substring is absent, the result is 0 (not -1, not NULL), which is handy for conditions in WHERE.

1-based indexing and the value 0

Because 0 means "not found", it doubles nicely as a filter:

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

-- Users with a real address: '@' is present
SELECT id, email
FROM users
WHERE POSITION('@' IN email) > 0;

Gotcha: POSITION finds only the first match and scans left to right. For a string like 'a@b@c' you get the position of the first @ (value 2); the second one is invisible this way. If either str or sub is NULL, the result is NULL too — a classic reason rows seem to "vanish" — so wrap nullable columns in COALESCE(email, '').

Pairing with SUBSTRING: splitting strings

A position on its own is rarely the goal — you usually feed it straight into SUBSTRING to carve out part of the string. The classic case: split an email into its local part and domain on the @.

-- 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;

The logic is simple: POSITION(...) - 1 is the length of the part before @, and POSITION(...) + 1 is the start just after it. The > 0 guard in WHERE protects against rows with no @, where - 1 would otherwise produce empty or garbled output.

The same trick works inside aggregates — for example, counting orders by user domain:

-- 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 and INSTR

MySQL supports POSITION(sub IN str) too (it is standard), but people more often write LOCATE or INSTR. Watch out for the different argument order:

-- 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

Key differences from PostgreSQL:

• LOCATE(sub, str) puts the needle first, while INSTR(str, sub) is the reverse — easy to mix up;
• LOCATE accepts a third argument, the start position: LOCATE('@', email, 3) searches from the third character, which lets you find a second occurrence;
• in ClickHouse the function is position(str, sub) (same order as STRPOS), plus positionCaseInsensitive for case-insensitive search.

Bottom line: in PostgreSQL reach for STRPOS for brevity or POSITION ... IN for portability, always remember the 1-based index and the 0 for a miss, and combine it with SUBSTRING to get a full string parser right inside SQL.