SQL Reference ยท Scalar Functions
SQL INSTR(), POSITION() and CHARINDEX() functions
Reference for finding a substring in SQL: INSTR, POSITION, CHARINDEX and LOCATE, the 1-based result, argument order differences and how to cut around a match.
These functions answer "where does this text start inside that text?" They return the 1-based position of the first match, or 0 when there is no match. With the position in hand you can cut a value apart using SUBSTR(), for example splitting an email at the @.
The standard form is POSITION(substring IN string). SQLite and Oracle call it INSTR(string, substring), SQL Server calls it CHARINDEX(substring, string), and MySQL has LOCATE(substring, string) as well as INSTR. The order of the arguments differs between them, which is the main trap.
SELECT INSTR('hello world', 'o') AS first_o; -- 5
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
INSTR(string, substring) POSITION(substring IN string) CHARINDEX(substring, string [, start]) LOCATE(substring, string [, start])
| Name | Description |
|---|---|
string |
The text to search in. |
substring |
The text to look for. |
start |
Optional (CHARINDEX, LOCATE, Oracle INSTR): where to begin searching. |
Returns: the position (starting at 1) of the first match, or 0 if not found. NULL if any argument is NULL.
Dialect differences
| Database | Function | Argument order | Case |
|---|---|---|---|
| Standard SQL | POSITION(sub IN s) |
substring first | by collation |
| SQLite | INSTR(s, sub) |
string first | case-sensitive |
| PostgreSQL | POSITION(sub IN s), STRPOS(s, sub) |
see each | case-sensitive |
| MySQL | INSTR(s, sub), LOCATE(sub, s [, start]), POSITION(sub IN s) |
INSTR: string first; LOCATE: substring first |
by collation (insensitive by default) |
| SQL Server | CHARINDEX(sub, s [, start]) |
substring first | by collation (insensitive by default) |
| Oracle | INSTR(s, sub [, start [, occurrence]]) |
string first | case-sensitive |
-- Standard SQL, PostgreSQL, MySQL SELECT POSITION('@' IN '[email protected]'); -- 4 -- SQL Server SELECT CHARINDEX('@', '[email protected]'); -- 4 -- Oracle: second occurrence of 'o' SELECT INSTR('hello world', 'o', 1, 2) FROM dual; -- 8
Usage notes
- Check for 0 before cutting.
SUBSTR(s, 1, INSTR(s, ' ') - 1)returns an empty string for a value with no space, because the length becomes0 - 1, which is-1, and other databases may raise an error for it. If you want the whole value in that case, wrap it in aCASE. - Existence test:
INSTR(col, 'text') > 0is the same ascol LIKE '%text%'without the wildcard rules (%and_are plain characters here). - Position counts characters for text; for blobs, SQLite counts bytes.
- The empty string is found at position 1 in SQLite.
- Case-insensitive search in SQLite:
INSTR(LOWER(s), LOWER(x)). - Last occurrence has no portable function. Oracle has a negative start (
INSTR(s, x, -1)), PostgreSQL can combineREVERSEandLENGTH, and in SQLite you can use a recursive query orREPLACEtricks. - Indexes are not used when you wrap a column in
INSTR.