Lesson 12: String Functions

In Lesson 11, we learned to calculate periods and change formats with date/time functions. In data analysis, tasks like "customer count by email domain" or "extracting brand from product name" are common. String functions let you slice, replace, and concatenate text.

String functions often have different names or argument orders across databases. This lesson uses SQLite as the default, with MySQL and PostgreSQL tabs shown where differences exist.

SUBSTR -- Extracting Part of a String

SUBSTR(string, start, length) -- start is 1-based. If length is omitted, it returns to the end.

-- Extract date part from order timestamp
SELECT
    order_number,
    ordered_at,
    SUBSTR(ordered_at, 1, 10) AS order_date,
    SUBSTR(ordered_at, 1, 7)  AS year_month
FROM orders
LIMIT 5;

Result:

-- Parse year/month/day from order number: ORD-20240315-00001
SELECT
    order_number,
    SUBSTR(order_number, 5, 4)  AS order_year,
    SUBSTR(order_number, 9, 2)  AS order_month,
    SUBSTR(order_number, 11, 2) AS order_day
FROM orders
LIMIT 3;

LENGTH

LENGTH(string) returns the number of characters.

-- Find products with particularly short or long names
SELECT name, LENGTH(name) AS name_length
FROM products
ORDER BY name_length DESC
LIMIT 5;

Result:

UPPER and LOWER

-- Normalize email to lowercase, display grade in uppercase
SELECT
    name,
    LOWER(email) AS email_lower,
    UPPER(grade) AS grade_display
FROM customers
LIMIT 5;

Result:

REPLACE

REPLACE(string, find, replacement) replaces all occurrences.

-- Phone number masking: show only last 4 digits
SELECT
    name,
    REPLACE(
        REPLACE(phone, SUBSTR(phone, 1, 9), '***-****-'),
        SUBSTR(phone, 1, 9), '***-****-'
    ) AS masked_phone
FROM customers
LIMIT 3;

-- Convert underscore-separated status values to readable format
SELECT DISTINCT
    status,
    REPLACE(status, '_', ' ') AS status_readable
FROM orders;

Result:

String Concatenation

-- Build a contact info string with ||
SELECT
    name,
    phone || ' — ' || email AS contact_info
FROM customers
LIMIT 5;

-- Build a contact info string with CONCAT()
SELECT
    name,
    CONCAT(phone, ' — ', email) AS contact_info
FROM customers
LIMIT 5;

Result:

-- Display SKU with category prefix
SELECT
    p.name,
    cat.name || '-' || p.sku AS display_sku
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
LIMIT 5;

-- Display SKU with category prefix
SELECT
    p.name,
    CONCAT(cat.name, '-', p.sku) AS display_sku
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
LIMIT 5;

Pattern Matching with LIKE

We covered this in Lesson 2, but here are more advanced pattern examples.

-- Users per email domain (after @)
SELECT DISTINCT
    SUBSTR(email, INSTR(email, '@') + 1) AS domain,
    COUNT(*) AS users
FROM customers
GROUP BY domain
ORDER BY users DESC
LIMIT 5;

-- Users per email domain (after @)
SELECT
    SUBSTRING(email, LOCATE('@', email) + 1) AS domain,
    COUNT(*) AS users
FROM customers
GROUP BY domain
ORDER BY users DESC
LIMIT 5;

-- Users per email domain (after @)
SELECT
    SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain,
    COUNT(*) AS users
FROM customers
GROUP BY domain
ORDER BY users DESC
LIMIT 5;

-- Products with numbers in model name
SELECT name
FROM products
WHERE name LIKE '%[0-9]%'   -- Standard SQL syntax
-- In SQLite, use GLOB for character classes:
-- WHERE name GLOB '*[0-9]*'
LIMIT 5;

TRIM, LTRIM, RTRIM

Removes leading/trailing whitespace (or specified characters).

-- Clean up accidental whitespace in product names
SELECT
    name,
    TRIM(name)   AS cleaned_name,
    LENGTH(name) - LENGTH(TRIM(name)) AS extra_chars
FROM products
WHERE LENGTH(name) != LENGTH(TRIM(name));

NULL and String Concatenation

The most common mistake in string concatenation: if one side is NULL, the entire result is NULL.

-- If birth_date is NULL, contact_info is also NULL!
SELECT
    name || ' (' || birth_date || ')' AS contact_info
FROM customers
LIMIT 5;

You must use COALESCE to replace NULL with a substitute string.

SELECT
    name || ' (' || COALESCE(birth_date, 'N/A') || ')' AS contact_info
FROM customers
LIMIT 5;

-- MySQL's CONCAT() also returns NULL when NULL is present
SELECT
    CONCAT(name, ' (', COALESCE(birth_date, 'N/A'), ')') AS contact_info
FROM customers
LIMIT 5;

-- CONCAT_WS() skips NULLs (with specified separator)
SELECT
    CONCAT_WS(' | ', name, phone, email) AS contact_info
FROM customers
LIMIT 5;

Rule: When concatenating strings, always wrap columns that can be NULL with COALESCE(col, 'default'). MySQL's CONCAT_WS() automatically skips NULLs, which is convenient.

LPAD, RPAD -- Fixed-Width Padding

Pads a string to a specified length, filling the shortfall with a specific character. Useful for order number formatting, report alignment, etc.

-- Zero-pad customer ID to 5 digits
SELECT
    id,
    printf('%05d', id) AS padded_id,
    name
FROM customers
LIMIT 5;

-- Zero-pad customer ID to 5 digits
SELECT
    id,
    LPAD(id, 5, '0') AS padded_id,
    name
FROM customers
LIMIT 5;

-- Fixed-width product name to 30 chars (right-pad with spaces)
SELECT RPAD(name, 30, ' ') AS fixed_name, price
FROM products
LIMIT 5;

-- Zero-pad customer ID to 5 digits
SELECT
    id,
    LPAD(id::text, 5, '0') AS padded_id,
    name
FROM customers
LIMIT 5;

-- Fixed-width product name to 30 chars
SELECT RPAD(name, 30, ' ') AS fixed_name, price
FROM products
LIMIT 5;

LPAD(value, total_length, fill_char) pads the left, RPAD pads the right. If the value is longer than total_length, MySQL/PG will truncate it.

GROUP_CONCAT / STRING_AGG -- String Aggregation by Group

Combines strings from multiple rows into one. A very commonly used function for "product list by category", "order numbers per customer", etc.

-- Product name list by supplier
SELECT
    s.company_name,
    GROUP_CONCAT(p.name, ', ') AS product_list,
    COUNT(*)                   AS product_count
FROM products AS p
INNER JOIN suppliers AS s ON p.supplier_id = s.id
WHERE p.is_active = 1
GROUP BY s.company_name
ORDER BY product_count DESC
LIMIT 3;

-- Product name list by supplier
SELECT
    s.company_name,
    GROUP_CONCAT(p.name ORDER BY p.name SEPARATOR ', ') AS product_list,
    COUNT(*) AS product_count
FROM products AS p
INNER JOIN suppliers AS s ON p.supplier_id = s.id
WHERE p.is_active = 1
GROUP BY s.company_name
ORDER BY product_count DESC
LIMIT 3;

-- Product name list by supplier
SELECT
    s.company_name,
    STRING_AGG(p.name, ', ' ORDER BY p.name) AS product_list,
    COUNT(*) AS product_count
FROM products AS p
INNER JOIN suppliers AS s ON p.supplier_id = s.id
WHERE p.is_active = 1
GROUP BY s.company_name
ORDER BY product_count DESC
LIMIT 3;

Result (example):

-- Customer names by grade (top 5 each)
SELECT
    grade,
    GROUP_CONCAT(name, ', ') AS sample_names
FROM (
    SELECT grade, name, ROW_NUMBER() OVER (PARTITION BY grade ORDER BY id) AS rn
    FROM customers
    WHERE is_active = 1
) AS ranked
WHERE rn <= 5
GROUP BY grade;

REGEXP -- Regular Expressions

LIKE's % and _ alone make it difficult to express complex patterns like "alphanumeric combinations" or "email format validation". Regular expressions enable much more precise pattern matching.

Database Support

-- Products with numbers in name (using GLOB)
SELECT name, price
FROM products
WHERE name GLOB '*[0-9]*'
LIMIT 5;

-- Products starting with uppercase letters
SELECT name
FROM products
WHERE name GLOB '[A-Z]*'
LIMIT 5;

-- Model number pattern: letters followed by numbers (e.g., RTX 5070, DDR5 16GB)
SELECT name, price
FROM products
WHERE name REGEXP '[A-Za-z]+[0-9]+'
LIMIT 5;

-- Basic email format validation (characters before and after @)
SELECT name, email
FROM customers
WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'
LIMIT 5;

-- Model number pattern: letters followed by numbers
SELECT name, price
FROM products
WHERE name ~ '[A-Za-z]+[0-9]+'
LIMIT 5;

-- Case-insensitive: products containing 'gaming' (using ~*)
SELECT name, price
FROM products
WHERE name ~* 'gaming'
LIMIT 5;

-- Basic email format validation
SELECT name, email
FROM customers
WHERE email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
LIMIT 5;

Syntax Comparison

Practical tip: REGEXP cannot use indexes, so for large datasets, narrow down with LIKE first, then use REGEXP for precise filtering.

Summary

Practice Problems

Problem 1

Create a customer contact card: concatenate each customer's name, phone, email into a single string in "name | phone | email" format. Return customer_id, contact_card, grade for all active customers, limited to 10 rows.

SELECT
    id AS customer_id,
    name || ' | ' || phone || ' | ' || email AS contact_card,
    grade
FROM customers
WHERE is_active = 1
LIMIT 10;

=== "MySQL"
    ```sql
    SELECT
        id AS customer_id,
        CONCAT(name, ' | ', phone, ' | ', email) AS contact_card,
        grade
    FROM customers
    WHERE is_active = 1
    LIMIT 10;
    ```

Problem 2

Extract the sequence number from each order (e.g., last 5 digits 00042 from ORD-20240315-00042) and display as integer. Return order_number, sequence_no (integer), total_amount, sorted by sequence_no descending, limited to 10 rows.

SELECT
    order_number,
    CAST(SUBSTR(order_number, -5) AS INTEGER) AS sequence_no,
    total_amount
FROM orders
ORDER BY sequence_no DESC
LIMIT 10;

Problem 3

Find the length of customer names and return the top 5 with the longest names. Sort by name_length descending.

SELECT
    name,
    LENGTH(name) AS name_length
FROM customers
ORDER BY name_length DESC
LIMIT 5;

Problem 4

Replace underscores (_) in order status with spaces and convert to uppercase. Show only unique status values. Return status (original) and display_status.

SELECT DISTINCT
    status,
    UPPER(REPLACE(status, '_', ' ')) AS display_status
FROM orders;

Problem 5

Query name and price for products containing the word 'Gaming' in their name, sorted by price descending. Use a LIKE pattern.

SELECT name, price
FROM products
WHERE name LIKE '%Gaming%'
ORDER BY price DESC;

Problem 6

Extract only the user ID portion before @ from customer emails. Return name, email, user_id, limited to 10 rows.

SELECT
    name,
    email,
    SUBSTR(email, 1, INSTR(email, '@') - 1) AS user_id
FROM customers
LIMIT 10;

=== "MySQL"
    ```sql
    SELECT
        name,
        email,
        SUBSTRING(email, 1, LOCATE('@', email) - 1) AS user_id
    FROM customers
    LIMIT 10;
    ```

=== "PostgreSQL"
    ```sql
    SELECT
        name,
        email,
        SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) AS user_id
    FROM customers
    LIMIT 10;
    ```

Problem 7

Create a short_name by truncating product names to the first 20 characters and appending ellipsis (...). If the name is 20 characters or less, show the original name. Return name, short_name, sorted by name length descending, limited to 10 rows.

SELECT
    name,
    CASE
        WHEN LENGTH(name) > 20
            THEN SUBSTR(name, 1, 20) || '...'
        ELSE name
    END AS short_name
FROM products
ORDER BY LENGTH(name) DESC
LIMIT 10;

=== "MySQL"
    ```sql
    SELECT
        name,
        CASE
            WHEN LENGTH(name) > 20
                THEN CONCAT(SUBSTRING(name, 1, 20), '...')
            ELSE name
        END AS short_name
    FROM products
    ORDER BY LENGTH(name) DESC
    LIMIT 10;
    ```

Problem 8

Convert customer grade to lowercase and name to uppercase to create a string in "GRADE: grade - NAME" format. Example: "VIP: vip - Kim". Return id, display_text, limited to 10 active customers.

SELECT
    id,
    UPPER(grade) || ': ' || LOWER(grade) || ' - ' || name AS display_text
FROM customers
WHERE is_active = 1
LIMIT 10;

=== "MySQL"
    ```sql
    SELECT
        id,
        CONCAT(UPPER(grade), ': ', LOWER(grade), ' - ', name) AS display_text
    FROM customers
    WHERE is_active = 1
    LIMIT 10;
    ```

Problem 9

From orders where order_number starts with 'ORD-2024', extract the date portion from the middle (characters 5-12, e.g., 20240315). Return order_number, date_part, total_amount, limited to 10 rows.

SELECT
    order_number,
    SUBSTR(order_number, 5, 8) AS date_part,
    total_amount
FROM orders
WHERE order_number LIKE 'ORD-2024%'
LIMIT 10;

Problem 10

Find the position of the @ symbol in product names (0 if not found). Target only products with spaces in their names, return name, space_pos (first space position), limited to 10 rows.

SELECT
    name,
    INSTR(name, ' ') AS space_pos
FROM products
WHERE INSTR(name, ' ') > 0
LIMIT 10;

=== "MySQL"
    ```sql
    SELECT
        name,
        LOCATE(' ', name) AS space_pos
    FROM products
    WHERE LOCATE(' ', name) > 0
    LIMIT 10;
    ```

=== "PostgreSQL"
    ```sql
    SELECT
        name,
        POSITION(' ' IN name) AS space_pos
    FROM products
    WHERE POSITION(' ' IN name) > 0
    LIMIT 10;
    ```

Problem 11

Concatenate customer's name and phone, displaying 'no number' when phone is NULL. Return customer_id, contact, limited to 10 rows.

SELECT
    id AS customer_id,
    name || ' — ' || COALESCE(phone, 'no number') AS contact
FROM customers
LIMIT 10;

SELECT
    id AS customer_id,
    CONCAT(name, ' — ', COALESCE(phone, 'no number')) AS contact
FROM customers
LIMIT 10;

Problem 12

Format customer ID with 6-digit zero-padding and a 'C-' prefix to create customer_code. Example: ID 42 -> 'C-000042'. Return customer_code, name, grade, limited to 10 rows.

SELECT
    'C-' || printf('%06d', id) AS customer_code,
    name,
    grade
FROM customers
LIMIT 10;

SELECT
    CONCAT('C-', LPAD(id, 6, '0')) AS customer_code,
    name,
    grade
FROM customers
LIMIT 10;

SELECT
    'C-' || LPAD(id::text, 6, '0') AS customer_code,
    name,
    grade
FROM customers
LIMIT 10;

Problem 13

For each category, concatenate the names of active products in that category with commas into a single row. Return category_name, product_list, product_count, sorted by product count descending, limited to 5 rows.

SELECT
    cat.name AS category_name,
    GROUP_CONCAT(p.name, ', ') AS product_list,
    COUNT(*) AS product_count
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE p.is_active = 1
GROUP BY cat.name
ORDER BY product_count DESC
LIMIT 5;

SELECT
    cat.name AS category_name,
    GROUP_CONCAT(p.name ORDER BY p.name SEPARATOR ', ') AS product_list,
    COUNT(*) AS product_count
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE p.is_active = 1
GROUP BY cat.name
ORDER BY product_count DESC
LIMIT 5;

SELECT
    cat.name AS category_name,
    STRING_AGG(p.name, ', ' ORDER BY p.name) AS product_list,
    COUNT(*) AS product_count
FROM products AS p
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE p.is_active = 1
GROUP BY cat.name
ORDER BY product_count DESC
LIMIT 5;

Problem 14

Find products whose names contain a model number pattern (letters immediately followed by digits, e.g., RTX5070, DDR5). Return name, price, sorted by price descending, limited to 10 rows.

SELECT name, price
FROM products
WHERE name GLOB '*[A-Za-z][0-9]*'
ORDER BY price DESC
LIMIT 10;

SELECT name, price
FROM products
WHERE name REGEXP '[A-Za-z][0-9]'
ORDER BY price DESC
LIMIT 10;

SELECT name, price
FROM products
WHERE name ~ '[A-Za-z][0-9]'
ORDER BY price DESC
LIMIT 10;

Scoring Guide

Problem Areas:

Next: Lesson 13: Numeric, Conversion, and Conditional Functions