Tracks With a Featured Artist

Easy
477.

Tracks With a Featured Artist

EasyStrings

MySQL

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

ColumnType
track_idint
titlevarchar
artist_namevarchar
duration_secsint

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).

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Track

track_idtitleartist_nameduration_secs
1Baarish (feat. Anuv Jain)Ritviz214
2SafarPrateek Kuhad198
3Raat (Live)The Local Train305
4Udd Gaye (feat. Divine)Raga Republic187
5Feather LightLost Stories241
6Dil (Remix) (feat. Neha Kakkar)Lost Stories222

Output

track_idtitlefeatured_artist
1Baarish (feat. Anuv Jain)Anuv Jain
4Udd Gaye (feat. Divine)Divine
6Dil (Remix) (feat. Neha Kakkar)Neha Kakkar
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
track_idint
titlevarchar
artist_namevarchar
duration_secsint

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_idtitleartist_nameduration_secs
1Baarish (feat. Anuv Jain)Ritviz214
2SafarPrateek Kuhad198
3Raat (Live)The Local Train305
4Udd Gaye (feat. Divine)Raga Republic187
5Feather LightLost Stories241
6Dil (Remix) (feat. Neha Kakkar)Lost Stories222

Output

track_idtitlefeatured_artist
1Baarish (feat. Anuv Jain)Anuv Jain
4Udd Gaye (feat. Divine)Divine
6Dil (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) > 0

Another 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 →