Find The Position Of A Substring Within A String

Locate where a substring first appears inside a string, returning its 1-based character position.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Find the position of @ in each email address

The @ in [email protected] is at position 6 (a=1, l=2, i=3, c=4, e=5, @=6). In [email protected] it is at position 4. In [email protected] it is at position 8. The key difference between engines is argument order: INSTR and STRPOS take (string, pattern) while SQL Server's CHARINDEX takes (pattern, string). A return value of 0 means the substring was not found.

MySQL MariaDB SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE contacts (id INT, email VARCHAR(100));

INSERT INTO
  contacts (id, email)
VALUES
  (1, '[email protected]'),
  (2, '[email protected]'),
  (3, '[email protected]');

Validated query

SQL

SELECT
  email,
  INSTR (email, '@') AS at_position
FROM
  contacts
ORDER BY
  id;

Expected result

email	at_position
[email protected]	6
[email protected]	4
[email protected]	8

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE contacts (id INT, email VARCHAR(100));

INSERT INTO
  contacts (id, email)
VALUES
  (1, '[email protected]'),
  (2, '[email protected]'),
  (3, '[email protected]');

Validated query

SQL

SELECT
  email,
  CHARINDEX ('@', email) AS at_position
FROM
  contacts
ORDER BY
  id;

Expected result

email	at_position
[email protected]	6
[email protected]	4
[email protected]	8

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE contacts (id INT, email VARCHAR(100));

INSERT INTO
  contacts (id, email)
VALUES
  (1, '[email protected]'),
  (2, '[email protected]'),
  (3, '[email protected]');

Validated query

SQL

SELECT
  email,
  STRPOS (email, '@') AS at_position
FROM
  contacts
ORDER BY
  id;

Expected result

email	at_position
[email protected]	6
[email protected]	4
[email protected]	8

MySQL/MariaDB/SQLite use INSTR(string, substr). PostgreSQL uses STRPOS(string, substr). SQL Server uses CHARINDEX(substr, string) — argument order is reversed. All return the same 1-based positions.

Useful when

Where this command helps.

validating that an email address contains an @ symbol
extracting the domain part of a URL by finding the position of a delimiter

Explanation

What the command is doing.

Finding the position of a substring is useful when you need to split strings, validate formats, or extract variable-length segments. MySQL, MariaDB, and SQLite use INSTR(string, substring). PostgreSQL uses STRPOS(string, substring). SQL Server uses CHARINDEX(substring, string) — note the argument order is reversed compared to INSTR. All functions return 1-based positions and return 0 if the substring is not found.

Find The Position Of A Substring Within A String

Find the position of @ in each email address

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.