Spotify: URL Slugs for Podcast Show Names — SQL Problem
The podcast team is moving shows to readable web addresses built from the show name.
- Difficulty: Easy
- Topics: Strings
- Dialect: MySQL
- Problem: #930
Problem statement
The podcast team is moving shows to readable web addresses built from the show name. Creators typed names by hand, so some have stray spaces at either end and some contain an apostrophe.
For every show, return show_id and url_slug: the name with leading and trailing spaces removed, apostrophes (') deleted, every remaining space replaced by a hyphen, and all letters in lower case. Names never contain two spaces in a row. Order by show_id.
A practice scenario set at Spotify: the tables and data are invented, and this is not a question from, or endorsed by, Spotify.
Tables
Table: PodcastShow
| Column | Type |
|---|---|
| show_id | int |
| show_name | varchar |
| host_name | varchar |
Primary key: show_id.
One row per podcast show, the name exactly as the creator entered it.
Examples
Example 1
PodcastShow
| show_id | show_name | host_name |
|---|---|---|
| 1 | Founders Unfiltered | Ishaan |
| 2 | Cricket After Hours | Kabir |
| 3 | Rohan's Mixtape | Rohan |
| 4 | The Daily Byte | Ananya |
| 5 | Aditi's Book Club | Aditi |
Output
| show_id | url_slug |
|---|---|
| 1 | founders-unfiltered |
| 2 | cricket-after-hours |
| 3 | rohans-mixtape |
| 4 | the-daily-byte |
| 5 | aditis-book-club |
How to solve Spotify: URL Slugs for Podcast Show Names
A slug is built by a chain of string functions, and the only real decision is their order. TRIM must come before the space-to-hyphen replacement: ' Cricket After Hours ' trimmed is 'Cricket After Hours', which becomes cricket-after-hours; replacing first would leave hyphens at both ends.
Apostrophes are deleted with REPLACE(x, '''', ''). The odd-looking literal is a string containing one apostrophe — inside single quotes, a quote is escaped by doubling it. So Rohan's Mixtape turns into rohans-mixtape. Lower-casing can happen at any point because it does not change spaces or apostrophes, and LOWER and LCASE are synonyms.
The alternatives apply the same steps in other orders — lower-case first, or deleting apostrophes before trimming — and give identical slugs, which is a good way to convince yourself which steps commute. Each row is processed on its own, so the query is a single scan plus the sort by show_id.
Reference solution (MySQL)
SELECT show_id,
LOWER(REPLACE(REPLACE(TRIM(show_name), '''', ''), ' ', '-')) AS url_slug
FROM PodcastShow
ORDER BY show_idAnother way
SELECT show_id, REPLACE(REPLACE(LCASE(TRIM(show_name)), ' ', '-'), '''', '') AS url_slug FROM PodcastShow ORDER BY show_idAnother way
SELECT show_id, LOWER(REPLACE(TRIM(REPLACE(show_name, '''', '')), ' ', '-')) AS url_slug FROM PodcastShow ORDER BY show_id← Spotify: Artist Merch Revenue From Shipped Orders · Spotify: Discover Weekly Save Rate per Listener →