INSTR()
SQL · String Functions
Theory
INSTR(string, substring) returns the 1-based position of the first occurrence of substring inside string, or 0 if it does not appear. INSTR('ravi@gmail.com', '@') is 5. It does the same job as POSITION but uses function-call style with the string first, which is easy to mix up when you switch between the two.
INSTR exists in SQLite, MySQL and Oracle. Oracle's version takes extra optional arguments for a start position and an occurrence number (for example the second '-'). SQL Server uses CHARINDEX(substring, string) and Postgres uses STRPOS(string, substring) or POSITION. The most common pattern is splitting a string at a separator: SUBSTR(email, 1, INSTR(email, '@') - 1) gives the username, and SUBSTR(email, INSTR(email, '@') + 1) gives the domain. Guard against 0 when the separator might be missing, or the slices give odd results.
Why we use it
Splitting strings at a separator is a daily cleaning task: user and domain from emails, area and number from phone codes, first and last name from a full name. INSTR finds the cut point for SUBSTR.
When to use it
Use INSTR with SUBSTR to split strings at a separator in SQLite, MySQL or Oracle. Use POSITION, STRPOS or CHARINDEX in other dialects. Some databases also have SPLIT_PART, which splits by a delimiter directly and is easier to read.
How it works
- Take the string and the substring
- Find the first place the substring appears
- Was it found? Yes: Return its 1-based position. No: Return 0.
- Use the position in SUBSTR: before = pos - 1, after = pos + 1
- Pieces of the string extracted
Syntax
INSTR(string, substring)Example
SELECT INSTR(email, '@') FROM employees;Key points
- Returns 0 when the substring is not found, not NULL
- Argument order is reversed compared to POSITION()
- SQL Server uses CHARINDEX(sub, str); Postgres uses STRPOS(str, sub)
- Oracle INSTR can take a start position and occurrence number
- Check for 0 before slicing, or missing separators break the split