Fix the Capitalisation of Registered Names — SQL Easy Problem

Problem statement

Students typed their names into the tech-fest registration form in whatever case they liked — aRJUN, PRIYA, kavya.

Return reg_id and student_name, with each name fixed so that only the first letter is a capital and every other letter is small, ordered by reg_id ascending. Every name is a single word made only of English letters.

Tables

Table: Registration

Primary key: reg_id.

One row per registration; student_name is exactly what the student typed, never NULL or empty.

Examples

Example 1

Registration

Output

How to solve Fix the Capitalisation of Registered Names

Split each name into its first character and the rest, fix the case of each part, and join them back:

• LEFT(student_name, 1) (or SUBSTRING(student_name, 1, 1)) is the first letter, and UPPER makes it a capital;
• SUBSTRING(student_name, 2) is everything from the second character on, and LOWER makes it small letters — for a two-letter name like om it is a single letter, and the method needs no special case;
• CONCAT joins the two parts.

Alias the result AS student_name, or the column is named after the whole expression. The comparison of your output with the expected one is case-sensitive, which is the point of the exercise: aRJUN must become exactly Arjun.

Another way to take the rest of the name is RIGHT(student_name, CHAR_LENGTH(student_name) - 1) — the last length − 1 characters. CHAR_LENGTH counts characters while MySQL's LENGTH counts bytes; for plain English letters they agree, but CHAR_LENGTH is the right habit for text, since a name with an accented or Devanagari letter takes more than one byte per character.

Finally ORDER BY reg_id gives the order the statement asks for. The work per row is constant, so the query is linear plus the sort.

Reference solution (MySQL)

SELECT reg_id,
       CONCAT(UPPER(LEFT(student_name, 1)), LOWER(SUBSTRING(student_name, 2))) AS student_name
FROM Registration
ORDER BY reg_id

Another way

SELECT reg_id, CONCAT(UCASE(SUBSTR(student_name, 1, 1)), LCASE(SUBSTR(student_name, 2))) AS student_name FROM Registration ORDER BY reg_id

Another way

SELECT reg_id, CONCAT(UPPER(LEFT(student_name, 1)), LOWER(RIGHT(student_name, CHAR_LENGTH(student_name) - 1))) AS student_name FROM Registration ORDER BY reg_id ASC