Tracks With a Featured Artist — SQL Strings Problem
On the music app, a collaboration is written into the track title as Song Name (feat. Guest Artist). The credits team wants the guest names pulled out.
- Difficulty: Easy
- Topics: Strings
- Dialect: MySQL
- Problem: #477
Problem statement
On the music app, a collaboration is written into the track title as Song Name (feat. Guest Artist). The credits team wants the guest names pulled out.
Return every track whose title contains (feat. , with the columns track_id, title and featured_artist — the text after (feat. and before the closing ). Titles without a featured artist are left out. Return the rows in any order.
Tables
Table: Track
| Column | Type |
|---|---|
| track_id | int |
| title | varchar |
| artist_name | varchar |
| duration_secs | int |
Primary key: track_id.
One row per track. A featured artist, when there is one, is always written once, at the end, as (feat. Name).
Examples
Example 1
Track
| track_id | title | artist_name | duration_secs |
|---|---|---|---|
| 1 | Baarish (feat. Anuv Jain) | Ritviz | 214 |
| 2 | Safar | Prateek Kuhad | 198 |
| 3 | Raat (Live) | The Local Train | 305 |
| 4 | Udd Gaye (feat. Divine) | Raga Republic | 187 |
| 5 | Feather Light | Lost Stories | 241 |
| 6 | Dil (Remix) (feat. Neha Kakkar) | Lost Stories | 222 |
Output
| track_id | title | featured_artist |
|---|---|---|
| 1 | Baarish (feat. Anuv Jain) | Anuv Jain |
| 4 | Udd Gaye (feat. Divine) | Divine |
| 6 | Dil (Remix) (feat. Neha Kakkar) | Neha Kakkar |
How to solve Tracks With a Featured Artist
Two string steps. The filter title LIKE '%(feat. %' keeps only collaborations — note the opening parenthesis is part of the marker, so a title like Feather Light is not caught by a looser %feat%.
To extract the guest, SUBSTRING_INDEX(title, '(feat. ', -1) returns everything after the marker, e.g. Neha Kakkar), and an outer SUBSTRING_INDEX(…, ')', 1) cuts at the first closing parenthesis. A title with another bracket before the marker, such as Dil (Remix) (feat. Neha Kakkar), still works because the inner call cuts at the marker itself, not at the first (.
With positions instead: LOCATE('(feat. ', title) finds where the marker starts; the guest begins 7 characters later (the marker's length) and ends one character before the end of the string, since the feature is always written last. INSTR is the same function with its arguments reversed. Every form is a constant amount of work per row.
Reference solution (MySQL)
SELECT track_id, title,
SUBSTRING_INDEX(SUBSTRING_INDEX(title, '(feat. ', -1), ')', 1) AS featured_artist
FROM Track
WHERE title LIKE '%(feat. %'Another way
SELECT track_id, title, SUBSTRING(title, LOCATE('(feat. ', title) + 7, CHAR_LENGTH(title) - LOCATE('(feat. ', title) - 7) AS featured_artist FROM Track WHERE LOCATE('(feat. ', title) > 0Another way
SELECT track_id, title, REPLACE(SUBSTRING(title, INSTR(title, '(feat. ') + 7), ')', '') AS featured_artist FROM Track WHERE INSTR(title, '(feat. ') > 0← Accounts With Two or More Kids Profiles · Subscriptions Renewing in the Next Seven Days →