Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT INSTR('hello world', 'o') AS first_o;   -- 5

Try it

SQL Positions, misses and case sensitivity

Press Run (or Ctrl + Enter) to see the result here.

SQL Splitting an email at the @ ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Filtering by position ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
SQL
-- 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 becomes 0 - 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 a CASE.
  • Existence test: INSTR(col, 'text') > 0 is the same as col 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 combine REVERSE and LENGTH, and in SQLite you can use a recursive query or REPLACE tricks.
  • Indexes are not used when you wrap a column in INSTR.