Cuts out part of a string: from the position you need and of the length you need.
SUBSTRING(<text> FROM <start>)
SUBSTRING(<text> FROM <start> FOR <length>)
SUBSTRING(<text>, <start>, <length>)
Positions are counted from 1. FROM — which character to start at, FOR — how many characters to take; without FOR, everything up to the end is taken. The comma form does the same.
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.
| first_part | rest |
|---|---|
| data | base |
| name | initial |
|---|---|
| Emma | E |
| Liam | L |
| Olivia | O |
| Noah | N |
| Sophia | S |
| 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 |
Where there's no phone number, the result is NULL too.
Positions start at 1, not 0. SUBSTRING('abc' FROM 0 FOR 2) returns just a: there's no “zeroth” character, but it still takes up a place in the length.
FOR is a length, not an end position: SUBSTRING('database' FROM 5 FOR 3) gives bas.
If you pass a pattern instead of a number, SUBSTRING searches with a regular expression: SUBSTRING('order #1234' FROM '[0-9]+') gives 1234.