POSITION()
SQL · String Functions
Theory
POSITION(substring IN string) returns the position where substring first appears inside string, counting from 1. POSITION('@' IN 'asha@techtown.in') returns 5 because '@' is the fifth character. If the substring is not found, the result is 0, and if either argument is NULL, the result is NULL. The search is case-sensitive in most databases.
POSITION is the SQL-standard spelling, supported by Postgres, MySQL and Snowflake. Other names do the same job: INSTR(string, substring) in SQLite, MySQL and Oracle (note the arguments are the other way round), CHARINDEX(substring, string) in SQL Server, and STRPOS(string, substring) in Postgres. On its own, POSITION is often used as a check: a result of 0 means the character is missing, which flags emails without '@'. More often it is used with SUBSTRING to cut a string at the found position.
Why we use it
The part you need often sits before or after a separator whose position changes from row to row, such as '@' in emails or '-' in codes. POSITION tells you exactly where the separator is so you can cut there, or flag rows where it is missing.
When to use it
Use POSITION to find a separator before slicing with SUBSTRING, or to check that a value contains something. For simple 'contains' filters, LIKE '%@%' is easier to read. Remember the argument order changes with INSTR and CHARINDEX.
How it works
- Take the substring to find and the string
- Scan the string from position 1
- Does the substring start at this position? Yes: Return the position. No: Move one character right.
- Reached the end without a match? Yes: Return 0. No: Keep scanning.
- Position returned (0 if not found)
Syntax
POSITION(substring IN string)Example
SELECT POSITION('@' IN email) FROM employees;Key points
- Returns 0 if the substring is not found, NULL if an input is NULL
- Positions start at 1
- SQLite/MySQL/Oracle: INSTR(str, sub); SQL Server: CHARINDEX(sub, str)
- Case-sensitive in most databases
- Usually combined with SUBSTRING to cut at the found position