Doctor Names for the Prescription Letterhead

Easy
204.

Doctor Names for the Prescription Letterhead

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

ColumnType
doctor_idint
first_namevarchar
middle_namevarchar
last_namevarchar
qualificationvarchar

Primary key: doctor_id.

middle_name is NULL for doctors who have none.

Example

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

Doctor

doctor_idfirst_namemiddle_namelast_namequalification
1RahulKrishnaSharmaMD
2SnehaNULLIyerMBBS
3AnanyaLakshmiMenonMS
4KabirNULLKhanDM

Output

doctor_idletterhead
1Dr. Rahul K. Sharma, MD
2Dr. Sneha Iyer, MBBS
3Dr. Ananya L. Menon, MS
4Dr. Kabir Khan, DM
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.

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

ColumnType
doctor_idint
first_namevarchar
middle_namevarchar
last_namevarchar
qualificationvarchar

Primary key: doctor_id.

middle_name is NULL for doctors who have none.

Examples

Example 1

Doctor

doctor_idfirst_namemiddle_namelast_namequalification
1RahulKrishnaSharmaMD
2SnehaNULLIyerMBBS
3AnanyaLakshmiMenonMS
4KabirNULLKhanDM

Output

doctor_idletterhead
1Dr. Rahul K. Sharma, MD
2Dr. Sneha Iyer, MBBS
3Dr. Ananya L. Menon, MS
4Dr. 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 Doctor

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

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