CHARINDEX / INSTR / POSITIONFunctions
Buy Me A Coffee

Search for a substring within a string and return the position of the first occurrence. Returns 0 or NULL if not found, depending on the engine.

Returns the 1-based position of a substring within a string, or 0 if not found. Standard SQL equivalent of MySQL LOCATE and SQL Server CHARINDEX.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all INSTR(str, substr) and LOCATE(substr, str [, start]) are the native functions. POSITION(substr IN str) also works as SQL-standard syntax. Returns 0 if not found (not NULL).
PostgreSQL ✓ Supported all POSITION(substr IN str) is the standard form. STRPOS(str, substr) is a PostgreSQL-specific alias with reversed argument order. Returns 0 if not found. No CHARINDEX or INSTR.
SQL Server ✓ Supported all CHARINDEX(needle, haystack [, start]) is the primary function. Returns 0 if not found. PATINDEX('%pattern%', str) supports LIKE-style wildcards. No INSTR or POSITION.
Oracle ✓ Supported all INSTR(str, substr [, start [, occurrence]]) is the primary function. Unique: supports nth-occurrence search and negative start position (search from end). Returns 0 if not found.
SQLite ✓ Supported all INSTR(haystack, needle) returns the position of the first occurrence. Returns 0 if not found. Argument order is reversed compared to SQL Server's CHARINDEX.

Details

Argument order is a constant gotcha: MySQL's LOCATE and SQL Server's CHARINDEX take (needle, haystack), while PostgreSQL's STRPOS and SQLite's INSTR take (haystack, needle). Oracle's INSTR is the most powerful, supporting nth-occurrence and reverse search. All engines return 0 (not NULL) when the substring is not found.

Standard Syntax

-- Standard SQL: POSITION('needle' IN haystack_col) -- Common alternatives: CHARINDEX('needle', haystack_col) INSTR(haystack_col, 'needle')

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

INSTR(str, substr) and LOCATE(substr, str [, start]) are the native functions. POSITION(substr IN str) also works as SQL-standard syntax. Returns 0 if not found (not NULL).

SELECT INSTR('hello world', 'world'); -- 7 SELECT LOCATE('world', 'hello world'); -- 7 SELECT LOCATE('world', 'hello world', 8); -- 0 (start after pos 8) SELECT POSITION('world' IN 'hello world'); -- 7

PostgreSQL

POSITION(substr IN str) is the standard form. STRPOS(str, substr) is a PostgreSQL-specific alias with reversed argument order. Returns 0 if not found. No CHARINDEX or INSTR.

SELECT POSITION('world' IN 'hello world'); -- 7 SELECT STRPOS('hello world', 'world'); -- 7

SQL Server

CHARINDEX(needle, haystack [, start]) is the primary function. Returns 0 if not found. PATINDEX('%pattern%', str) supports LIKE-style wildcards. No INSTR or POSITION.

SELECT CHARINDEX('world', 'hello world'); -- 7 SELECT CHARINDEX('world', 'hello world', 8); -- 0 SELECT PATINDEX('%wor%', 'hello world'); -- 7

Oracle

INSTR(str, substr [, start [, occurrence]]) is the primary function. Unique: supports nth-occurrence search and negative start position (search from end). Returns 0 if not found.

SELECT INSTR('hello world', 'l') FROM DUAL; -- 3 SELECT INSTR('hello world', 'l', 1, 2) FROM DUAL; -- 4 (2nd occurrence) SELECT INSTR('hello world', 'l', -1) FROM DUAL; -- 10 (search from end)

SQLite

INSTR(haystack, needle) returns the position of the first occurrence. Returns 0 if not found. Argument order is reversed compared to SQL Server's CHARINDEX.

SELECT INSTR('hello world', 'world'); -- 7 SELECT INSTR('hello world', 'xyz'); -- 0