SQL String Functions: SUBSTRING, SPLIT_PART, CONCAT, and LIKE in Interviews

String functions turn stored text into comparison keys, parsed fields, display labels, and ordered lists. The important decision is whether a string is free text or a structured value with a documented format.

Normalize only what the analysis contract treats as equivalent. Parse defensively when delimiters can be absent, preserve NULL when information is missing, and state ordering whenever several strings become one result.

Normalize before grouping

Whitespace and letter case should not split email domains in this analysis. The CASE expression also keeps missing or delimiter-free addresses in a NULL group instead of pretending they have a valid domain.

Input: contacts

WITH normalized AS (
  SELECT
    contact_id,
    CASE
      WHEN POSITION('@' IN TRIM(email)) > 0
      THEN LOWER(SPLIT_PART(TRIM(email), '@', 2))
      ELSE NULL
    END AS email_domain
  FROM contacts
)
SELECT
  email_domain,
  COUNT(*) AS contact_count
FROM normalized
GROUP BY email_domain
ORDER BY email_domain NULLS LAST;

Output

Normalization is not universal cleaning. Lowercasing may be right for domains and wrong for case-sensitive identifiers. The SQL GROUP BY guide covers the resulting aggregate grain.

Parse structured strings with explicit rules

SUBSTRING with a regular expression can extract components when a reference format is known. Rows that do not contain the requested numeric components return NULL for those fields.

Input: ticket_refs

SELECT
  ref_id,
  SUBSTRING(reference FROM '^[A-Z]+') AS prefix,
  SUBSTRING(reference FROM '[0-9]{4}') AS year_text,
  SUBSTRING(reference FROM '[0-9]+$') AS sequence_text
FROM ticket_refs
ORDER BY ref_id;

Output

SPLIT_PART is useful when delimiters define the structure. Here it finds the host, while SUBSTRING keeps the complete path and stops before a query string. A missing path becomes /.

Input: web_requests

WITH without_scheme AS (
  SELECT
    request_id,
    REGEXP_REPLACE(url, '^[^:]+://', '') AS remainder
  FROM web_requests
)
SELECT
  request_id,
  SPLIT_PART(remainder, '/', 1) AS host,
  COALESCE(
    SUBSTRING(remainder FROM '/[^?]*'),
    '/'
  ) AS path
FROM without_scheme
ORDER BY request_id;

Output

Concatenation and aggregation need NULL rules

The || operator propagates NULL, while CONCAT_WS skips NULL arguments. Converting empty trimmed values to NULL produces usable display names without extra spaces.

Input: contacts

SELECT
  contact_id,
  CONCAT_WS(
    ' ',
    NULLIF(TRIM(first_name), ''),
    NULLIF(TRIM(last_name), '')
  ) AS display_name
FROM contacts
ORDER BY contact_id;

Output

STRING_AGG combines several rows. Its internal ORDER BY defines list order, and DISTINCT removes tags that become equal after normalization.

Input: article_tags

SELECT
  article_id,
  STRING_AGG(
    DISTINCT LOWER(TRIM(tag)),
    ', '
    ORDER BY LOWER(TRIM(tag))
  ) AS tag_list
FROM article_tags
GROUP BY article_id
ORDER BY article_id;

Output

The difference between distinct input values and distinct result rows is covered in SQL DISTINCT. SQL ORDER BY covers the two ordering scopes.

Escape LIKE wildcards when they are literal

In a LIKE pattern, % matches any number of characters and _ matches one character. ESCAPE '\' makes the following wildcard literal, so the query can test for actual underscore and percent characters.

Input: product_codes

SELECT
  product_id,
  code,
  code LIKE '%\_%' ESCAPE '\' AS has_literal_underscore,
  code LIKE '%\%%' ESCAPE '\' AS has_literal_percent
FROM product_codes
ORDER BY product_id;

Output

ILIKE adds case-insensitive matching in PostgreSQL. It does not change wildcard or escaping rules.

Split delimited text into ordered rows

STRING_TO_TABLE expands one delimited value into several rows. WITH ORDINALITY records each token's original position. The blank token in response 4 is filtered, so its surviving green token keeps position 3.

Input: survey_responses

SELECT
  s.response_id,
  t.option,
  t.position
FROM survey_responses AS s
CROSS JOIN LATERAL STRING_TO_TABLE(
  s.selected_options,
  ';'
) WITH ORDINALITY AS t(option, position)
WHERE t.option <> ''
ORDER BY s.response_id, t.position;

Output

Repeated multi-value analysis is usually easier with one value per stored row. When a delimited field is unavoidable at an ingestion boundary, parse once, retain source position if it matters, and validate blank and NULL tokens. More exercises are in SQL practice questions.

FAQ

What does SPLIT_PART return when a field is missing?

PostgreSQL returns an empty string when the requested field number is beyond the available pieces. Check delimiter presence or convert an empty result with NULLIF when missing should be NULL.

How does CONCAT_WS handle NULL?

It skips NULL arguments and inserts the separator only between remaining values. Empty strings are not NULL, so normalize them first when they should be treated as missing.

Does STRING_AGG guarantee order?

Only when its aggregate call includes an ORDER BY. The query's final ORDER BY controls result rows, not the order of values inside each aggregate string.

What do percent and underscore mean in LIKE?

Percent matches zero or more characters. Underscore matches exactly one character. Define an escape character when either symbol must be matched literally.

Should delimited text be stored in one column?

Not when the values need frequent filtering, joining, constraints, or aggregation. A child table with one row per value is usually a clearer relational shape; delimited strings are often best treated as an ingestion or display format.