Extract the first word of a title
Your task
Return title and the portion before the first space as first_word. When there is no space, return the entire title. Sort by album ID.
INSTR finds the position of the first occurrence of some text. SUBSTR can use that position as a boundary.
Sample data
albums| id | title | genre | price | stock |
|---|---|---|---|---|
| 1 | Night Bus | Jazz | 18 | 7 |
| 2 | Static Bloom | Indie | 22 | 4 |
| 3 | Soft Circuit | Electronic | 24 | 0 |
| 4 | Blue Hour | Jazz | 16 | 3 |
| 5 | Coastline | Indie | 19 | 8 |
| 6 | Low Tide | Ambient | 21 | 2 |
Expected output
| title | first_word |
|---|---|
| Night Bus | Night |
| Static Bloom | Static |
| Soft Circuit | Soft |
| Blue Hour | Blue |
| Coastline | Coastline |
| Low Tide | Low |
Show a hint
Use CASE to handle INSTR(title, ' ') = 0 separately.
Expressions and missing values guide
Calculate a value for each row
Expressions can combine columns, constants, and functions. This query estimates how much stock is worth at the listed price. AS gives the calculation a name in the result.
SELECT title, price * stock AS stock_value
FROM albums;Build a category from conditions
CASE tests branches from top to bottom. Put the most specific test first: zero would also satisfy stock < 5. ELSE catches the remaining rows.
SELECT title,
CASE
WHEN stock = 0 THEN 'out of stock'
WHEN stock < 5 THEN 'low stock'
ELSE 'stocked'
END AS availability
FROM albums;Account for missing values
NULL represents an unknown or missing value. Compare it with IS NULL rather than equality. NULLIF can also turn a known sentinel into NULL. AVG ignores NULL, so these two averages use different sets of albums.
SELECT AVG(stock) AS all_albums,
AVG(NULLIF(stock, 0)) AS in_stock_only
FROM albums;Worked solution
SELECT title, CASE WHEN INSTR(title, ' ') = 0 THEN title ELSE SUBSTR(title, 1, INSTR(title, ' ') - 1) END AS first_word FROM albums ORDER BY id;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
