String and date functions, and CASE expressions

Real queries rarely just fetch raw columns as-is — trimming a string, formatting a date, or branching between a few possible outputs are all things SQL does directly, in the query itself, instead of leaving them entirely to application code.

Beginner

4 min read

String functions: shaping text inside the query

SELECT UPPER(name) FROM users;                          -- "ADA LOVELACE"
SELECT LOWER(email) FROM users;                          -- "ada@example.com"
SELECT LENGTH(name) FROM users;                           -- 12
SELECT CONCAT(first_name, ' ', last_name) FROM users;      -- "Ada Lovelace"
SELECT TRIM('  Ada  ');                                     -- "Ada" — leading/trailing whitespace removed
SELECT SUBSTRING(email, 1, 3) FROM users;                    -- "ada" — first 3 characters

These are ordinary string operations, the same ones covered for Python strings elsewhere in this app, just running inside the database instead of after fetching the data into application code. Doing this work in SQL matters for one concrete reason: it happens once, on the database's own hardware, on however many rows match — pulling raw data into Python and then transforming every row there means transferring more data over the network and doing the work in a slower, less specialized place.

Pattern matching with LIKE

SELECT * FROM users WHERE email LIKE '%@gmail.com';    -- ends with @gmail.com
SELECT * FROM users WHERE name LIKE 'A%';                -- starts with A
SELECT * FROM users WHERE name LIKE '_da';                -- exactly 3 chars, ending in "da"

LIKE matches text against a pattern using two wildcards: % matches any sequence of characters (including none), and _ matches exactly one character. This is deliberately simpler and less powerful than full regular expressions (which most databases also support separately, via a different operator) — LIKE covers the large majority of real "starts with," "ends with," "contains" needs without the complexity regex brings.

Date and time functions

SELECT NOW();                                             -- the current timestamp
SELECT CURRENT_DATE;                                       -- today's date, no time component
SELECT order_date + INTERVAL '7 days' FROM orders;           -- a date one week later
SELECT EXTRACT(YEAR FROM order_date) FROM orders;             -- just the year, as a number
SELECT DATE_TRUNC('month', order_date) FROM orders;             -- rounds down to the start of that month

DATE_TRUNC is the mechanism directly behind the QuerySet aggregation lesson's TruncMonth example — "total revenue per month" needs every order's timestamp rounded down to its containing month before grouping, and DATE_TRUNC('month', ...) is exactly that rounding operation, expressed in raw SQL. Date arithmetic (+ INTERVAL '7 days') lets the database compute date math directly, rather than fetching raw dates and computing offsets in application code.

CASE: an if/elif/else expression inside a query

SELECT
    name,
    CASE
        WHEN age < 13 THEN 'child'
        WHEN age < 20 THEN 'teenager'
        ELSE 'adult'
    END AS age_group
FROM users;

CASE WHEN ... THEN ... ELSE ... END is SQL's conditional expression — checked top to bottom, same as Python's if/elif/else chain, returning the value from the first matching WHEN, or the ELSE value if none match. This runs per row, computing a value that doesn't exist as a stored column at all — age_group here is entirely derived, computed fresh for every row the query touches.

CASE inside an aggregate: conditional counting

SELECT
    COUNT(CASE WHEN amount > 100 THEN 1 END) AS large_orders,
    COUNT(CASE WHEN amount <= 100 THEN 1 END) AS small_orders
FROM orders;

Combining CASE with an aggregate function is a genuinely common, slightly non-obvious pattern: CASE WHEN amount > 100 THEN 1 END returns 1 for large orders and NULL (the implicit ELSE, when none is given) for everything else — and since COUNT skips NULL values entirely (from the NULL lesson), this counts only the rows where the condition was true, in a single pass over the table, without needing two separate queries or a GROUP BY.

Why these belong in SQL rather than always in application code

# Doing it in Python instead:
users = User.objects.all()
for user in users:
    display_name = user.name.strip().title()   # every row, after fetching all of them

Nothing stops formatting, trimming, or categorizing data in application code instead — but doing it in the database means the transformation happens once, close to the data, on rows the database was already touching, rather than fetching every raw row across the network first and then processing each one individually in a loop. This isn't a strict "always do it in SQL" rule — logic that's specific to how the application presents data, or that needs to call out to something outside the database, still belongs in application code — but formatting, filtering, and categorizing based on data already in the table are usually cheaper and simpler to express directly in the query.