SQL CHARINDEX / POSITION / INSTR: Find String Position (All Databases)

Overview

Find where a substring appears within a string. Every database has this function but calls it something different.

MySQL

-- LOCATE(substring, string):
SELECT LOCATE('@', 'user@example.com');  -- 5

-- LOCATE with start position:
SELECT LOCATE('o', 'Hello World', 5);  -- 8 (starts searching at pos 5)

-- INSTR also works in MySQL:
SELECT INSTR('Hello World', 'World');  -- 7

-- Extract domain from email:
SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM users;

PostgreSQL

-- POSITION(substring IN string):
SELECT POSITION('@' IN 'user@example.com');  -- 5

-- STRPOS(string, substring):
SELECT STRPOS('Hello World', 'World');  -- 7

-- Extract domain:
SELECT SPLIT_PART(email, '@', 2) AS domain FROM users;
-- Or:
SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1) FROM users;

SQL Server

-- CHARINDEX(substring, string, start):
SELECT CHARINDEX('@', 'user@example.com');  -- 5
SELECT CHARINDEX('o', 'Hello World', 5);    -- 8

-- PATINDEX with wildcards:
SELECT PATINDEX('%@%', 'user@example.com');  -- 5
SELECT PATINDEX('%[0-9]%', 'abc123');        -- 4 (first digit)

-- Extract domain:
SELECT SUBSTRING(email, CHARINDEX('@', email) + 1, 100) FROM users;

Oracle

-- INSTR(string, substring, start, occurrence):
SELECT INSTR('user@example.com', '@') FROM DUAL;  -- 5

-- Find Nth occurrence:
SELECT INSTR('a.b.c.d', '.', 1, 2) FROM DUAL;  -- 4 (2nd dot)

-- Extract domain:
SELECT SUBSTR(email, INSTR(email, '@') + 1) FROM users;

Skip the Syntax Lookup

Instead of memorizing CHARINDEX / POSITION / INSTR syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Frequently Asked Questions