Splits a string by a separator and returns the part with the number you need.
SPLIT_PART(<text>, <separator>, <part number>)
Cuts the text into pieces at the separator and returns the piece with the given number, counting from 1. A negative number counts from the end: -1 is the last part. Handy for text with a predictable structure: the domain of an email, the country code of a phone number, part of a path.
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.
| login | domain |
|---|---|
| emma | example.com |
| last_part |
|---|
| v2 |
| name | phone | country_code |
|---|---|---|
| Emma | +44 7700 900123 | +44 |
| Liam | NULL | NULL |
| Olivia | +44 7700 900456 | +44 |
| Noah | +44 7700 900789 | +44 |
| Sophia | NULL | NULL |
Unlike SUBSTRING, you don't need to know the length of the code: everything up to the first space is taken.
If there's no part with that number, the result is an empty string '', not NULL: SPLIT_PART('a,b', ',', 5).
Part number 0 is an error, field position must not be zero: numbering starts at 1.
Only the separator itself is cut, surrounding spaces stay: SPLIT_PART('a, b', ',', 2) gives b with a leading space. TRIM will remove it.