SQL SUBSTRING
Extracts a specific slice of a string, given a starting position and (optionally) a length — the general-purpose text-slicing tool.
What & Why
SUBSTRING(text FROM start FOR length) pulls out a piece of a string, starting at a given position (1-indexed, not 0-indexed) and running for a given number of characters. Omitting FOR length takes everything from the start position to the end of the string.
See How It Works
BUSINESS QUESTION
Marketing wants three SUBSTRING slices beside every lead email: a fixed prefix, the domain, and the complete email name.
| id | campaign_id | created_at | qualified_at | converted_at | lead_score | source | country | |
|---|---|---|---|---|---|---|---|---|
| 301 | 1 | ana@example.com | 2024-01-21 09:10:00+00 | 2024-01-22 11:00:00+00 | 2024-02-02 10:00:00+00 | 86 | google_ads | US |
| 302 | 1 | ben@example.com | 2024-01-24 12:40:00+00 | NULL | NULL | 52 | google_ads | CA |
| 303 | 2 | chloe@example.com | 2024-02-16 08:30:00+00 | 2024-02-18 14:20:00+00 | NULL | 74 | GB | |
| 304 | 3 | dev@example.com | 2024-03-20 17:15:00+00 | 2024-03-21 09:00:00+00 | 2024-04-04 16:00:00+00 | 91 | US |
Watch the same emails produce three different slicesStep 1 of 3
SUBSTRING(email FROM 1 FOR 4)
| id | email_prefix | |
|---|---|---|
| 301 | ana@example.com | ana@ |
| 302 | ben@example.com | ben@ |
| 303 | chloe@example.com | chlo |
| 304 | dev@example.com | dev@ |
A fixed start and length return the first four characters from each real lead email.
EXAMPLE QUERY
SELECT
id,
email,
SUBSTRING(email FROM 1 FOR 4) AS email_prefix,
SUBSTRING(email FROM POSITION('@' IN email) + 1) AS email_domain,
SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) AS email_name
FROM marketing.leads
WHERE POSITION('@' IN email) > 0
ORDER BY id;Sign up free to try it on a real business scenario