Finds the character where a substring starts within a string.
POSITION(<substring> IN <text>)
STRPOS(<text>, <substring>)
Returns the number of the character where the substring first starts, counting from 1. If there's no such substring — 0. Often used together with SUBSTRING or LEFT to cut out a piece before or after a certain character.
employees
| name | department | salary | bonus | phone |
|---|---|---|---|---|
| Emma | backend | 1200 | 300 | +44 7700 900123 |
| Liam | backend | 1000 | NULL | NULL |
| Olivia | frontend | 1350 | 150 | +44 7700 900456 |
| Noah | frontend | 950 | NULL | +44 7700 900789 |
| Sophia | design | 1200 | 200 | NULL |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| at_sign | not_found |
|---|---|
| 5 | 0 |
| name | phone | first_space |
|---|---|---|
| Emma | +44 7700 900123 | 4 |
| Liam | NULL | NULL |
| Olivia | +44 7700 900456 | 4 |
| Noah | +44 7700 900789 | 4 |
| Sophia | NULL | NULL |
| login |
|---|
| emma |
@ is the fifth character, so we take 4 characters from the left. For this task there's also the shorter SPLIT_PART.
Not found means 0, not NULL. Check POSITION(…) > 0.
Case matters: POSITION('E' IN 'emma') gives 0. To ignore case, bring both sides to the same case with LOWER.
STRPOS takes its arguments in a different order: the text first, then what to look for — STRPOS('emma@example.com', '@').