LIKE (Function) / POSITION

POSITION: SQL standard function for finding character positions (MySQL, PostgreSQL).

POSITION(search_string IN target_string)

INSTR: Arguments are in the opposite order (MySQL, Oracle).

INSTR(target_string, search_string)

CHARINDEX: Used in SQL Server.

CHARINDEX(search_string, target_string [, start_position])

LOCATE: Equivalent to INSTR (MySQL).

LOCATE(search_string, target_string [, start_position])

Function	DBMS	Description
POSITION(s IN str)	MySQL, PostgreSQL	Returns the position of the first occurrence of s in str. Returns 0 if not found.
INSTR(str, s)	MySQL, Oracle	Returns the position of the first occurrence of s in str. Arguments are in the opposite order from POSITION.
CHARINDEX(s, str)	SQL Server	Returns the position of the first occurrence of s in str. Returns 0 if not found.
LOCATE(s, str, pos)	MySQL	Returns the position of the first occurrence of s in str, starting from pos (optional).

The following examples use the employees table.

Check whether an email address contains @.

sample_position.sql

SELECT
    email,
    POSITION('@' IN email) AS at_position
FROM employees;

+--------------------------+-------------+
| email                    | at_position |
+--------------------------+-------------+
| ayanami.rei@example.com  |          12 |
| ikari.shinji@example.com |          13 |
| soryu.asuka@example.com  |          12 |
+--------------------------+-------------+
3 rows in set

Use the position of @ to extract the domain part.

sample_position.sql

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

+--------------------------+-------------+
| email                    | domain      |
+--------------------------+-------------+
| ayanami.rei@example.com  | example.com |
| ikari.shinji@example.com | example.com |
| soryu.asuka@example.com  | example.com |
+--------------------------+-------------+
3 rows in set

Find the position of the first dot in each email address (POSITION > 0 checks for existence).

sample_position.sql

SELECT
    email,
    POSITION('.' IN email) AS first_dot_pos
FROM employees
WHERE POSITION('.' IN email) > 0;

+--------------------------+---------------+
| email                    | first_dot_pos |
+--------------------------+---------------+
| ayanami.rei@example.com  |             8 |
| ikari.shinji@example.com |             6 |
| soryu.asuka@example.com  |             6 |
+--------------------------+---------------+
3 rows in set

POSITION is available in MySQL and PostgreSQL (SQL standard syntax).

-- MySQL / PostgreSQL: use POSITION.
SELECT email, POSITION('@' IN email) AS at_position FROM employees;

In Oracle, use INSTR. The argument order is the opposite of POSITION — you can also pass a third argument for the start position and a fourth for which occurrence to find.

-- Oracle: use INSTR (argument order: target_string, search_string).
SELECT email, INSTR(email, '@') AS at_position FROM employees;

In SQL Server, use CHARINDEX. The argument order is: search_string, target_string.

-- SQL Server: use CHARINDEX.
SELECT email, CHARINDEX('@', email) AS at_position FROM employees;

In SQLite, INSTR is available with the same argument order as MySQL.

-- MySQL / SQLite: use INSTR (argument order: target_string, search_string).
SELECT email, INSTR(email, '@') AS at_position FROM employees;

POSITION returns character positions starting from 1. If the search string is not found, it returns 0, so you can check for existence with a condition like "greater than 0".

In MySQL, both POSITION and INSTR are available, but their argument orders differ. POSITION takes "search_string IN target_string", while INSTR takes "target_string, search_string". Be careful not to mix them up.

You can combine the returned position with SUBSTRING to flexibly extract substrings based on a specific pattern. For string replacement, see UPPER / LOWER / REPLACE.