Postgres in depth · Chapter 17 of 65

Cleaning student names

By the end of this chapter you can take text apart and put it back together in Postgres: join pieces, cut out parts, change case, build lines with format, and find and replace patterns with regular expressions. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Answering LCO's questions
  • 4 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.

Why you need this

LCO stores each student's name, email and phone exactly once, the way the student typed them. Every system around LCO wants them in another shape. The certificate printer wants first and last names apart. Sana's SMS gateway refuses +91 9947631743: it takes ten digits and nothing else. Priya wants to know which course slugs no longer match their titles, and the support screen needs a one-line label per student, even for the ones with no city.

Storing every shape would mean copies that drift apart. Instead the query reshapes the text when it is asked for, with Postgres's text functions.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    name-parts.sql: first name, last name, initials

    The certificate printer wants every name in parts. Every LCO name is a first name, one space, and a last name. For every student who has not been deleted (deleted_at is empty), return the name, first_name (the part before the space), last_name (the part after it) and initials (the first letter of each part, together, like AS for Aarav Shah).

    Return four columns: name, first_name, last_name, initials. Order: last_name A to Z, then first_name A to Z.

  2. Task 2

    sms-numbers.sql: ten digits for the SMS gateway

    Sana's SMS gateway takes a mobile number as exactly ten digits, with no country code, spaces or plus sign: +91 9947631743 must become 9947631743. For every student who has a phone and has not been deleted, return the name and mobile (those ten digits, as text).

    Return two columns: name, mobile. Order: name A to Z.

    Careful: students with no phone have no number to send; leave them out rather than returning an empty mobile.

  3. Task 3

    slug-check.sql: slugs that no longer match their titles

    Priya's rule for a course slug: take the title in lowercase, replace every run of characters that are not a lowercase letter or a digit with one hyphen, then remove any hyphen left at the start or the end. List the courses whose slug is not what the rule gives.

    Return three columns: title, slug (as stored) and suggested (what the rule gives). Order: title A to Z.

    Careful: Next.js has a dot and C++ has two plus signs. A run of several such characters becomes one hyphen, and a hyphen at the end must go.

  4. Task 4 · interview question

    Interview: contact-line.sql, a label for every student

    The support screen shows one line per student: the name, then a comma, a space and the city, like Aarav Shah, Mumbai. A student with no city shows only the name, like Tara Menon, with no comma. For every student who has not been deleted, return the name and line.

    Return two columns: name, line. Order: name A to Z.

    Careful: the obvious way to join the pieces turns the whole line into NULL when the city is missing.

What you will learn

  • Joining text: ||, concat and the NULL that swallows a line.
  • Text functions: upper, lower, initcap, length, trim, left, right, substr, position, split_part, replace, lpad.
  • format: lines built from a template, and SQL built safely.
  • Regular expressions: ~ and ~*, substring with a pattern, and regexp_replace.

More in Answering LCO's questions

Build "Cleaning student names" yourself

LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.