Tidy Up Pet Names

An animal shelter's intake form let volunteers type pet names however they liked, so the adoption website shows names such as bISCUIT and PEPPER.

Return pet_id and pet_name, where pet_name is rewritten so that its first character is upper case and every other character is lower case (only the very first character of the value is capitalized, so 'MR-WHISKERS' becomes 'Mr-whiskers'; characters that are not letters stay as they are).

Include every pet, ordered by pet_id ascending. The names contain plain ASCII letters, digits and hyphens only, with no leading spaces. An empty table returns no rows.

Tables

shelter_pets — One row per animal currently listed for adoption.

ColumnTypeDescription
pet_id (key)INTEGERUnique id
pet_nameTEXTName as typed by a volunteer, in any mix of upper and lower case
speciesTEXT'dog', 'cat', 'rabbit' or 'parrot'

Example

shelter_pets

pet_idpet_namespecies
1PEPPERcat
2Juniperdog
4bISCUITdog
7mochirabbit
9mR-WHISKERScat

Expected output

pet_idpet_name
1Pepper
2Juniper
4Biscuit
7Mochi
9Mr-whiskers

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Strings & CASE · problem 55 of 70 · SQLite

SUBSTR(text, start, length) is 1-indexed in SQLite: SUBSTR(x, 1, 1) is the first character.
SUBSTR(x, 2) with no length returns everything from the second character to the end.
Join the two parts with ||, applying UPPER to the first and LOWER to the rest.

The idea

Split each name into two pieces: the first character, SUBSTR(pet_name, 1, 1), and everything after it, SUBSTR(pet_name, 2). Upper-case the first piece, lower-case the second, and glue them back together with SQLite's concatenation operator ||.

Positions in SQLite strings start at 1, not 0, which is why the remainder begins at 2. Leaving out the length argument means "to the end of the string", so names of any length work, including one-letter names where the remainder is simply empty. UPPER and LOWER leave digits and hyphens alone.

The required order is by pet_id, so an explicit ORDER BY pet_id is needed: without it SQLite may return rows in any order.

The alternative lower-cases the whole name first and then upper-cases its first character, assembling the result with printf. Same result, different order of operations.

Solution

SELECT pet_id,
       UPPER(SUBSTR(pet_name, 1, 1)) || LOWER(SUBSTR(pet_name, 2)) AS pet_name
FROM shelter_pets
ORDER BY pet_id;

Another way

SELECT pet_id,
       printf('%s%s', upper(substr(lower(pet_name), 1, 1)), substr(lower(pet_name), 2)) AS pet_name
FROM shelter_pets
ORDER BY pet_id;

Why common answers fail

Capitalizes the first letter but never lowers the rest, so 'PEPPER' stays 'PEPPER'.

SELECT pet_id, UPPER(SUBSTR(pet_name, 1, 1)) || SUBSTR(pet_name, 2) AS pet_name
FROM shelter_pets
ORDER BY pet_id;

SQLite strings are 1-indexed: SUBSTR(pet_name, 1) is the whole name, so the first letter appears twice ('Ppepper').

SELECT pet_id, UPPER(SUBSTR(pet_name, 1, 1)) || LOWER(SUBSTR(pet_name, 1)) AS pet_name
FROM shelter_pets
ORDER BY pet_id;

Sorts by the name instead of by pet_id, so the row order is wrong.

SELECT pet_id, UPPER(SUBSTR(pet_name, 1, 1)) || LOWER(SUBSTR(pet_name, 2)) AS pet_name
FROM shelter_pets
ORDER BY pet_name;

What the interviewer is checking

A warm-up that checks basic string functions, 1-based indexing and whether you honour the requested sort order.

Mistakes to avoid

  • Upper-casing the first letter without lowering the rest.
  • Treating SUBSTR as 0-indexed and repeating or dropping a character.
  • Forgetting ORDER BY, or sorting by the cleaned name.

Follow-ups to expect

  • Capitalize every hyphen-separated part ('Mr-Whiskers'): split with instr and a recursive CTE, or chain replace calls for each letter after a hyphen.
  • Persist the fix with an UPDATE ... SET pet_name = UPPER(SUBSTR(pet_name, 1, 1)) || LOWER(SUBSTR(pet_name, 2)).
  • Trim stray spaces first (wrap pet_name in TRIM()).

In real work

Cleaning user-entered names, cities and product titles before display or deduplication is routine data-quality work in every customer database.

Practice next

Next Post Previous Post
No Comment
Add Comment
comment url