Doctor Names for the Prescription Letterhead — SQL Problem
The printed prescription pad shows each doctor as Dr. <first> <middle initial>. <last>, <qualification> — for example Dr. Rahul K. Sharma, MD.
- Difficulty: Easy
- Topics: Strings, Conditional Logic
- Dialect: MySQL
- Problem: #204
Problem statement
The printed prescription pad shows each doctor as Dr. <first> <middle initial>. <last>, <qualification> — for example Dr. Rahul K. Sharma, MD. A doctor with no middle name (NULL) is printed without the initial: Dr. Sneha Iyer, MBBS.
Return doctor_id and letterhead for every doctor, in any order. Names are already stored in the right case; the initial is the first letter of the middle name.
Tables
Table: Doctor
| Column | Type |
|---|---|
| doctor_id | int |
| first_name | varchar |
| middle_name | varchar |
| last_name | varchar |
| qualification | varchar |
Primary key: doctor_id.
middle_name is NULL for doctors who have none.
Examples
Example 1
Doctor
| doctor_id | first_name | middle_name | last_name | qualification |
|---|---|---|---|---|
| 1 | Rahul | Krishna | Sharma | MD |
| 2 | Sneha | NULL | Iyer | MBBS |
| 3 | Ananya | Lakshmi | Menon | MS |
| 4 | Kabir | NULL | Khan | DM |
Output
| doctor_id | letterhead |
|---|---|
| 1 | Dr. Rahul K. Sharma, MD |
| 2 | Dr. Sneha Iyer, MBBS |
| 3 | Dr. Ananya L. Menon, MS |
| 4 | Dr. Kabir Khan, DM |
How to solve Doctor Names for the Prescription Letterhead
The letterhead is string assembly with one optional part. The interesting question is how to make the middle initial disappear cleanly — no NULL., and no double space between first and last name.
Two MySQL behaviours combine neatly. CONCAT(LEFT(middle_name, 1), '.') is NULL whenever middle_name is NULL, because CONCAT propagates NULL. CONCAT_WS(' ', …) joins its arguments with a space but skips NULL ones, so the first name, optional initial and last name come out as Rahul K. Sharma or Sneha Iyer with exactly one space between the parts. Wrapping that in an outer CONCAT adds the Dr. prefix and the qualification.
The explicit version is a CASE with one branch per shape, or IFNULL(CONCAT(initial, '. '), '') to turn the missing part into an empty string. All three are row-by-row and read the table once.
Reference solution (MySQL)
SELECT doctor_id,
CONCAT('Dr. ', CONCAT_WS(' ', first_name, CONCAT(LEFT(middle_name, 1), '.'), last_name), ', ', qualification) AS letterhead
FROM DoctorAnother way
SELECT doctor_id, CASE WHEN middle_name IS NULL THEN CONCAT('Dr. ', first_name, ' ', last_name, ', ', qualification) ELSE CONCAT('Dr. ', first_name, ' ', SUBSTRING(middle_name, 1, 1), '. ', last_name, ', ', qualification) END AS letterhead FROM DoctorAnother way
SELECT doctor_id, CONCAT('Dr. ', first_name, ' ', IFNULL(CONCAT(LEFT(middle_name, 1), '. '), ''), last_name, ', ', qualification) AS letterhead FROM Doctor← Label Each Insurance Claim by How It Settled · Average Consultation Fee by Specialty →