POSITION()

Syntax

How it works

POSITION() is a string function that reports where a substring first appears inside another string. Standard SQL spells it POSITION(substring IN string), and it sits in the same family of text tools as SUBSTRING() and REPLACE(). You can browse the whole set in the SQL functions reference.

The catch is that every engine names this operation differently. The SQL standard form POSITION(substring IN string) works in PostgreSQL and MySQL. MySQL, Oracle and SQLite also expose INSTR(string, substring). MySQL adds LOCATE(substring, string [, start]), and SQL Server uses CHARINDEX(substring, string [, start]). Despite the different spellings they behave the same way: all are 1-based, and all return 0 when the substring is not found.

The two everyday uses are splitting and testing. Combine POSITION() with SUBSTRING() to cut a value around a delimiter, such as pulling the domain out of an email address. Or compare the result against zero, as in POSITION('@' IN email) > 0, to check whether a string contains something. For a categorised tour of the rest, see the SQL functions list.

Examples

Find the position of a character in an email

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

email        | at_pos
-------------+-------
ali@shop.com |      4
noreply@x.io |      8

Split around a delimiter with POSITION and SUBSTRING

-- everything after the '@' is the email domain
SELECT email,
       SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;

email        | domain
-------------+---------
ali@shop.com | shop.com
noreply@x.io | x.io

The same search in every dialect

-- PostgreSQL and MySQL (SQL standard)
SELECT POSITION('@' IN email) FROM users;

-- MySQL, Oracle, SQLite
SELECT INSTR(email, '@') FROM users;

-- MySQL only
SELECT LOCATE('@', email) FROM users;

-- SQL Server
SELECT CHARINDEX('@', email) FROM users;

engine     | call                   | result
-----------+------------------------+-------
PostgreSQL | POSITION('@' IN email) |      4
MySQL      | LOCATE('@', email)     |      4
SQLite     | INSTR(email, '@')      |      4
SQL Server | CHARINDEX('@', email)  |      4

Check for existence by testing the result

-- keep only rows whose value contains an '@'
SELECT email
FROM users
WHERE POSITION('@' IN email) > 0;

email
------------
ali@shop.com
noreply@x.io

Find a later occurrence with a start position

-- MySQL: find the second dot by starting after the first
SELECT LOCATE('.', 'a.b.c', LOCATE('.', 'a.b.c') + 1) AS second_dot;

second_dot
----------
         4

Common mistakes

-- LOCATE takes the substring first, then the string.
-- These arguments are the wrong way round.
SELECT LOCATE(email, '@') FROM users;

-- Substring first, then the string to search in.
SELECT LOCATE('@', email) FROM users;

-- Feeding a possible 0 straight into SUBSTRING.
-- When 'X' is missing, POSITION returns 0.
SELECT SUBSTRING(code FROM POSITION('X' IN code)) FROM parts;

-- 0 means not found, so guard for it first.
SELECT CASE WHEN POSITION('X' IN code) > 0
            THEN SUBSTRING(code FROM POSITION('X' IN code) + 1)
            ELSE code
       END AS suffix
FROM parts;

-- Assuming a NULL column gives 0.
SELECT POSITION('@' IN email) FROM users;  -- NULL email yields NULL

-- Coalesce first when the column can be NULL.
SELECT POSITION('@' IN COALESCE(email, '')) FROM users;

Performance

POSITION() and its cousins scan a string character by character, so a standard B-tree index cannot speed up an arbitrary substring search. A predicate like POSITION('abc' IN name) > 0 forces a full scan of the column, exactly like LIKE '%abc%'. If the match is anchored to the start of the value, prefer LIKE 'abc%' so the optimiser can use the index.

For repeated substring lookups on large tables, reach for a purpose built tool: a full text index in MySQL or SQL Server, or a trigram index (pg_trgm) in PostgreSQL. If you only ever slice out a fixed part of a value, a generated column plus an index on it lets the engine answer without recomputing POSITION() each time.

Interview questions

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