sqlcmd.net validated sql reference

Command guide

intermediate string-processing MySQL MariaDB SQL Server PostgreSQL SQLite

Find The Position Of A Substring Within A String

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

Created Last updated 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 alice@example.com is at position 6 (a=1, l=2, i=3, c=4, e=5, @=6). In bob@company.net it is at position 4. In noreply@support.io 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, 'alice@example.com'),
  (2, 'bob@company.net'),
  (3, 'noreply@support.io');
Validated query
SQL
SELECT
  email,
  INSTR (email, '@') AS at_position
FROM
  contacts
ORDER BY
  id;
Expected result
emailat_position
alice@example.com6
bob@company.net4
noreply@support.io8
SQL Server
Engine-specific syntax
Source table data
Setup
CREATE TABLE contacts (id INT, email VARCHAR(100));

INSERT INTO
  contacts (id, email)
VALUES
  (1, 'alice@example.com'),
  (2, 'bob@company.net'),
  (3, 'noreply@support.io');
Validated query
SQL
SELECT
  email,
  CHARINDEX ('@', email) AS at_position
FROM
  contacts
ORDER BY
  id;
Expected result
emailat_position
alice@example.com6
bob@company.net4
noreply@support.io8
PostgreSQL
Engine-specific syntax
Source table data
Setup
CREATE TABLE contacts (id INT, email VARCHAR(100));

INSERT INTO
  contacts (id, email)
VALUES
  (1, 'alice@example.com'),
  (2, 'bob@company.net'),
  (3, 'noreply@support.io');
Validated query
SQL
SELECT
  email,
  STRPOS (email, '@') AS at_position
FROM
  contacts
ORDER BY
  id;
Expected result
emailat_position
alice@example.com6
bob@company.net4
noreply@support.io8

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.

Tags

stringinstrcharindexstrpospositionsearch

Related commands

Keep moving through nearby tasks.

Official documentation