SQL Quest › SQL Interview Questions › String Functions
Extract Family Names (SUBSTR + INSTR)
The passenger registry stores names as 'LastName, Mr. FirstName' (last name before the comma, honorific and first name after). For a demographics view, extract just the last name part.
Show passenger_id, name, and SUBSTR(name, 1, INSTR(name, ',') - 1) AS last_name. Order by passenger_id. Limit to 15 rows.
INSTR(haystack, needle) returns the 1-indexed position of the first match (or 0 if not found). SUBSTR(string, start, length) extracts a substring. Combine them when you need a piece of a string up to (or after) a delimiter — this is the classic "split on comma" pattern in engines that don't have a SPLIT_PART function.
Solve it in the browser editor →
Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".
Schema
passengers
| passenger_id | survived | pclass | name | sex | age | sibsp | parch | fare | embarked |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 0 | 3 | Braund, Mr. Owen Harris | male | 22 | 1 | 0 | 7.25 | S |
| 2 | 1 | 1 | Cumings, Mrs. John Bradley | female | 38 | 1 | 0 | 71.28 | C |
| 3 | 1 | 3 | Heikkinen, Miss. Laina | female | 26 | 0 | 0 | 7.93 | S |
Expected output: last_name = 'Braund'
Hint
Concepts
SELECT SUBSTR INSTR String Functions
Practise the topic: SQL practice questions · String function practice
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test