0Pricing
SQL Interview Prep · Lesson

Parsing and Formatting Strings

SUBSTRING, position, splitting, and case conversion across dialects.

Parsing and Formatting Strings is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why String Functions Get Asked

Real data is messy: full names that need splitting, email domains to extract, codes embedded in IDs, inconsistent casing. Interviewers use string manipulation to see if you can clean and reshape text without exporting to a script.

As with dates, the functions are only loosely standardized, so the goal is to know the concept and the common variants.

  • Substring extraction and position
  • Concatenation
  • Splitting and replacing
  • Case conversion and trimming

SUBSTRING and Position

SUBSTRING(s FROM start FOR length) is the SQL-standard form; most engines also accept SUBSTRING(s, start, length). String positions are 1-based, a classic off-by-one trap for programmers used to 0-based languages.

POSITION(sub IN s) (or STRPOS/CHARINDEX) finds where a substring begins, returning 0 when not found.

SELECT
  SUBSTRING('INV-2024-042' FROM 5 FOR 4) AS year,   -- '2024'
  POSITION('-' IN 'INV-2024-042')        AS first_dash; -- 4

Extracting an Email Domain

A staple worked example. Find the @, then take everything after it. Combining POSITION with SUBSTRING is the portable approach.

In PostgreSQL you can also use SPLIT_PART(email, '@', 2), which reads more cleanly and is worth mentioning as the idiomatic answer.

-- Portable
SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users;

-- Postgres idiom
SELECT SPLIT_PART(email, '@', 2) AS domain FROM users;

Concatenation Across Dialects

Joining strings is dialect-sensitive, and interviewers expect you to know the variants.

  • SQL standard / Postgres / Oracle: the || operator.
  • MySQL: CONCAT(a, b, c) (the || operator is logical OR by default).
  • SQL Server: + for strings, or CONCAT().

CONCAT() treats NULL as an empty string, while || and + typically make the whole result NULL if any operand is NULL, a subtle bug source.

-- Postgres
SELECT first_name || ' ' || last_name AS full_name FROM people;

-- MySQL / SQL Server
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM people;

The NULL Concatenation Trap

Following the previous scene: if last_name is NULL, then first_name || ' ' || last_name yields NULL in Postgres, wiping out the whole name.

The defensive fix is COALESCE on nullable parts, or use CONCAT_WS (concat with separator), which skips NULLs and inserts the separator only between present values.

-- Safe in Postgres / MySQL
SELECT CONCAT_WS(' ', first_name, last_name) AS full_name FROM people;

-- Or guard each part
SELECT first_name || ' ' || COALESCE(last_name, '') FROM people;

Splitting Strings

"Pull the third segment out of a hyphenated code" tests splitting. PostgreSQL's SPLIT_PART(s, delim, n) returns the nth piece directly and is the cleanest tool.

MySQL has no direct split; the idiom is nested SUBSTRING_INDEX: take the first n parts, then the last one of those.

-- Postgres
SELECT SPLIT_PART('a-b-c-d', '-', 3); -- 'c'

-- MySQL: third part of a-b-c-d
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a-b-c-d', '-', 3), '-', -1); -- 'c'

Replace, Trim and Pad

Cleaning operations interviewers expect on sight:

  • REPLACE(s, from, to) swaps all occurrences.
  • TRIM(s) removes leading/trailing spaces; TRIM(BOTH 'x' FROM s) removes a specific character.
  • LPAD(s, len, ch) / RPAD pad to a fixed width, handy for zero-padding IDs.
SELECT
  REPLACE('555.123.4567', '.', '-') AS phone,   -- 555-123-4567
  TRIM('   hello   ')               AS clean,   -- 'hello'
  LPAD('42', 6, '0')                AS padded;   -- '000042'

Case Conversion and Length

Normalizing case is essential before comparing or grouping text. UPPER and LOWER are universal; INITCAP (Postgres/Oracle) title-cases words.

LENGTH(s) returns character count in most engines, but watch out: SQL Server uses LEN(), and in some configs LENGTH counts bytes for multibyte text. Mention this nuance for non-ASCII data.

SELECT
  LOWER(email)        AS email_norm,
  INITCAP(city)       AS city_pretty,  -- Postgres
  LENGTH(description)  AS chars
FROM places;

Pattern Matching Beyond LIKE

When LIKE is not powerful enough, interviewers like to see regex awareness. PostgreSQL offers the ~ operator and REGEXP_REPLACE / REGEXP_MATCHES; MySQL has REGEXP / REGEXP_SUBSTR.

Example: keep only digits from a phone string. Regex makes this a one-liner versus a chain of REPLACE calls.

-- Postgres: strip non-digits
SELECT REGEXP_REPLACE('(555) 123-4567', '[^0-9]', '', 'g')
  AS digits; -- '5551234567'

Deeper Example: Normalize and Dedupe Names

A combined cleaning task. Suppose names arrive with stray spaces and mixed case, causing false duplicates. Normalize first, then group.

Trim, collapse internal whitespace with regex, and lowercase before counting distinct values. This is exactly the kind of multi-step reasoning interviewers reward.

SELECT
  LOWER(REGEXP_REPLACE(TRIM(name), '\s+', ' ', 'g')) AS norm_name,
  COUNT(*) AS occurrences
FROM contacts
GROUP BY 1
ORDER BY occurrences DESC;

Casting Strings to Numbers and Dates

String columns often hold values that should be numbers or dates. CAST(s AS INTEGER) or the Postgres shorthand s::int converts, but fails on bad input.

For dates, TO_DATE(s, 'YYYY-MM-DD') (Postgres/Oracle) parses with an explicit format mask, the safest approach because it removes ambiguity about day/month order.

SELECT
  CAST(qty_text AS INTEGER)            AS qty,
  TO_DATE(order_str, 'DD/MM/YYYY')     AS order_date
FROM staging;

Quick Check

Reason about NULL behavior in string concatenation.

Recap: Parsing and Formatting Strings

Key points to carry into the interview:

  • String positions are 1-based; SUBSTRING + POSITION extract by location.
  • Concatenate with || (Postgres), CONCAT (MySQL), or + (SQL Server) and remember NULL propagation; prefer CONCAT_WS.
  • Split with SPLIT_PART (Postgres) or nested SUBSTRING_INDEX (MySQL).
  • REPLACE, TRIM, LPAD, UPPER/LOWER clean and normalize; regex handles the hard cases.
  • Normalize case and whitespace before grouping to avoid false duplicates.

Frequently asked questions

Is the “Parsing and Formatting Strings” lesson free?

Yes — the full text of “Parsing and Formatting Strings” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Parsing and Formatting Strings”?

SUBSTRING, position, splitting, and case conversion across dialects. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Parsing and Formatting Strings” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Date Arithmetic and Intervals
  2. Truncating and Bucketing Dates
  3. Parsing and Formatting Strings
  4. Time Zones and Timestamps
← Back to SQL Interview Prep