Work E-mail Addresses for New Joiners — SQL Strings Problem
IT creates a mailbox for each new joiner before day one. The address is the first name, a dot and the last name, all in lower case with any spaces removed,…
- Difficulty: Easy
- Topics: Strings, Conditional Logic
- Dialect: MySQL
- Problem: #226
Problem statement
IT creates a mailbox for each new joiner before day one. The address is the first name, a dot and the last name, all in lower case with any spaces removed, followed by @acmecorp.in — Ravi De Souza becomes ravi.desouza@acmecorp.in. Some joiners have a single name and last_name NULL; their address is just the first name, @acmecorp.in (no dot).
Return joiner_id and work_email for every joiner. Return the rows in any order.
Tables
Table: NewJoiner
| Column | Type |
|---|---|
| joiner_id | int |
| first_name | varchar |
| last_name | varchar |
| joining_date | date |
Primary key: joiner_id.
One row per person with an accepted offer. last_name is NULL for people who go by one name; a last name may contain a space.
Examples
Example 1
NewJoiner
| joiner_id | first_name | last_name | joining_date |
|---|---|---|---|
| 1 | Ravi | De Souza | 2025-07-01 |
| 2 | Priya | Iyer | 2025-07-01 |
| 3 | Anbu | NULL | 2025-07-07 |
| 4 | Sara | Van Der Berg | 2025-07-14 |
| 5 | Harsh | Mehta | 2025-07-14 |
Output
| joiner_id | work_email |
|---|---|
| 1 | ravi.desouza@acmecorp.in |
| 2 | priya.iyer@acmecorp.in |
| 3 | anbu@acmecorp.in |
| 4 | sara.vanderberg@acmecorp.in |
| 5 | harsh.mehta@acmecorp.in |
How to solve Work E-mail Addresses for New Joiners
Building the address is string plumbing: LOWER(first_name), a dot, LOWER(REPLACE(last_name, ' ', '')) and the domain, joined with CONCAT.
The NULL last name is the catch. In MySQL CONCAT returns NULL as soon as any argument is NULL, so the naive expression gives a single-name joiner no address at all. The solution branches with CASE WHEN last_name IS NULL, leaving out both the dot and the last name in that branch.
Two neater forms avoid the branch. CONCAT_WS('.', first_name, last_name_without_spaces) joins its non-NULL arguments with the separator and simply skips a NULL — with no stray dot — and the result is lower-cased once. Or build the '.' + last name piece with CONCAT, which is NULL exactly when the last name is, and turn it into an empty string with IFNULL. Each is one pass over the table with constant work per row.
Reference solution (MySQL)
SELECT joiner_id,
CASE WHEN last_name IS NULL
THEN CONCAT(LOWER(first_name), '@acmecorp.in')
ELSE CONCAT(LOWER(first_name), '.', LOWER(REPLACE(last_name, ' ', '')), '@acmecorp.in')
END AS work_email
FROM NewJoinerAnother way
SELECT joiner_id, CONCAT(LOWER(CONCAT_WS('.', first_name, REPLACE(last_name, ' ', ''))), '@acmecorp.in') AS work_email FROM NewJoinerAnother way
SELECT joiner_id, CONCAT(LOWER(first_name), IFNULL(CONCAT('.', LOWER(REPLACE(last_name, ' ', ''))), ''), '@acmecorp.in') AS work_email FROM NewJoiner← Employees Without a Reachable Emergency Contact · Late Punch-Ins per Employee in March 2025 →