String, date & CASE WHEN

Chapter 8 15 code blocks 4004 words

Real data is messy. Names have stray spaces, dates arrive as text, categories need to be derived from numbers. This chapter is the toolbox for fixing all of that inside the query, before the data ever reaches pandas.

CASE WHEN comes first because it is the single most useful construct here — it is SQL's if / elif / else.

CASE WHEN

CASE walks its conditions top to bottom and returns the value of the first one that is TRUE. If none match, it returns the ELSE value — or NULL if there is no ELSE.

SELECT
    enrollment_id,
    grade,
    CASE
        WHEN grade IS NULL  THEN 'not graded'
        WHEN grade >= 90    THEN 'A'
        WHEN grade >= 80    THEN 'B'
        WHEN grade >= 70    THEN 'C'
        WHEN grade >= 60    THEN 'D'
        ELSE 'F'
    END AS letter_grade
FROM enrollments
WHERE grade IS NOT NULL
ORDER BY grade DESC
LIMIT 6;

6 rows

1 ms

:::gotcha ⚠️ Order matters — and it is why you don't need upper bounds Students often write WHEN grade >= 80 AND grade < 90 THEN 'B'. The upper bound is unnecessary: by the time SQL reaches that line, every grade of 90 or more has already matched and left. Writing the conditions from highest to lowest makes the whole thing simpler.

Now reverse the order and watch it break: put WHEN grade >= 60 THEN 'D' first, and every passing grade becomes a D, because 100 also satisfies >= 60. Have students make this mistake once on purpose. :::

The two forms of CASE

-- Searched CASE: any condition. Use this 95% of the time.
CASE WHEN status = 'completed' THEN 'done'
     WHEN status = 'active'    THEN 'in progress'
     ELSE 'other' END

-- Simple CASE: shorter, but only tests equality against one expression
CASE status
     WHEN 'completed' THEN 'done'
     WHEN 'active'    THEN 'in progress'
     ELSE 'other' END

:::gotcha ⚠️ Simple CASE cannot match NULL CASE status WHEN NULL THEN ... never fires, because it compiles to status = NULL — UNKNOWN, forever. Only the searched form can test WHEN status IS NULL. When NULLs are possible, use the searched form. :::

CASE in ORDER BY — custom sort order

Alphabetical order is rarely the order a human wants. CASE lets you define your own:

SELECT enrollment_id, status
FROM enrollments
ORDER BY CASE status
             WHEN 'active'    THEN 1
             WHEN 'completed' THEN 2
             WHEN 'failed'    THEN 3
             WHEN 'dropped'   THEN 4
             ELSE 5
         END,
         enrollment_id;

… first 15 of 2500 rows

2500 rows

2 ms

Another common trick — force NULLs to the end regardless of the database's default:

ORDER BY CASE WHEN city IS NULL THEN 1 ELSE 0 END, city;

CASE inside aggregates (pivot) This is the most valuable pattern in the whole chapter. Putting CASE inside SUM or COUNT turns rows into columns — a pivot table, in plain SQL.

SELECT
    course_id,
    COUNT(*)                                                  AS total,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END)     AS completed,
    SUM(CASE WHEN status = 'active'    THEN 1 ELSE 0 END)     AS active,
    SUM(CASE WHEN status = 'failed'    THEN 1 ELSE 0 END)     AS failed,
    SUM(CASE WHEN status = 'dropped'   THEN 1 ELSE 0 END)     AS dropped
FROM enrollments
GROUP BY course_id
ORDER BY course_id;

14 rows

1 ms

course_idtotalcompletedactivefaileddropped

152120 255000 332001 432100 541300

Compare this with GROUP BY course_id, status from Chapter 4: that gives one row per combination and misses the empty ones. This gives a proper grid, with real zeros. It is what a manager actually wants to look at.

The same pattern gives conditional percentages — the pass-rate idiom from Chapter 4:

SELECT
    course_id,
    COUNT(*) AS graded,
    ROUND(100.0 * SUM(CASE WHEN grade >= 60 THEN 1 ELSE 0 END) / COUNT(*), 1) AS pass_pct
FROM enrollments WHERE grade IS NOT NULL
GROUP BY course_id;

14 rows

1 ms

String functions

FunctionDoesExample → result

UPPER(s) / LOWER(s)Change caseUPPER('aziza') → AZIZA LENGTH(s)Number of charactersLENGTH('Aziza') → 5 SUBSTR(s, start, len)Extract part (1-indexed!)SUBSTR('Karimova',1,3) → Kar REPLACE(s, from, to)Replace all occurrencesREPLACE('a-b-c','-','.') → a.b.c TRIM(s) / LTRIM / RTRIMRemove surrounding spacesTRIM(' hi ') → hi INSTR(s, sub)Position of substring, 0 if absentINSTR('a@b.uz','@') → 2 a || bConcatenate'A' || 'B' → AB

SELECT
    first_name || ' ' || last_name                          AS full_name,
    SUBSTR(first_name,1,1) || SUBSTR(last_name,1,1)         AS initials,
    LOWER(first_name) || '.' || LOWER(last_name) || '@school.uz' AS email,
    LENGTH(first_name || last_name)                         AS name_length
FROM students
LIMIT 4;

4 rows

0 ms

:::gotcha ⚠️ SQL strings start at 1, not 0 Every Python programmer gets this wrong once. SUBSTR(s, 1, 3) is the first three characters — SUBSTR(s, 0, 3) does something surprising and different. There is no zero-based indexing anywhere in SQL. :::

Dialect note: string function names are one of the least portable parts of SQL.

Cleaning messy text

The two most common real-world text problems, and how to fix both in the WHERE clause:

-- Problem 1: invisible leading/trailing spaces.
-- 'Tashkent ' <> 'Tashkent', so this row silently never matches.
SELECT * FROM students WHERE TRIM(city) = 'Tashkent';

-- Problem 2: inconsistent capitalisation ('TASHKENT', 'tashkent', 'Tashkent')
SELECT * FROM students WHERE LOWER(TRIM(city)) = 'tashkent';

-- Both at once, as a cleaned output column
SELECT
    city                                   AS raw,
    UPPER(SUBSTR(TRIM(LOWER(city)),1,1))
      || SUBSTR(TRIM(LOWER(city)),2)       AS cleaned   -- 'Tashkent'
FROM students;

Result 1

… first 15 of 82 rows

82 rows

Result 2

… first 15 of 82 rows

82 rows

Result 3

… first 15 of 300 rows

300 rows

2 ms

:::gotcha ⚠️ Cleaning in WHERE costs you the index WHERE LOWER(TRIM(city)) = 'tashkent' is correct but forces a full scan (Chapter 3's non-sargable rule). For a one-off analysis that is fine. For a query that runs constantly, the real fix is to clean the data on the way in — an UPDATE once (Chapter 10), or a CHECK constraint that refuses dirty values in the first place. :::

A quick way to find dirty values before you decide what to do:

SELECT city, COUNT(*) AS n,
       CASE WHEN city <> TRIM(city) THEN 'has spaces' ELSE 'clean' END AS issue
FROM students
GROUP BY city
ORDER BY n DESC;

9 rows

0 ms

Date and time functions SQLite stores dates as 'YYYY-MM-DD' text and provides five functions to work with them.

SELECT
    enrolled_at,
    strftime('%Y',    enrolled_at) AS year,
    strftime('%Y-%m', enrolled_at) AS year_month,
    strftime('%w',    enrolled_at) AS weekday_num,
    CASE strftime('%w', enrolled_at)
        WHEN '0' THEN 'Sunday'   WHEN '1' THEN 'Monday'
        WHEN '2' THEN 'Tuesday'  WHEN '3' THEN 'Wednesday'
        WHEN '4' THEN 'Thursday' WHEN '5' THEN 'Friday'
        ELSE 'Saturday'
    END AS weekday
FROM enrollments
WHERE enrollment_id IN (1, 13, 19);

3 rows

0 ms

SELECT
    DATE('2026-02-14', '+7 days')            AS week_later,      -- 2026-02-21
    DATE('2026-02-14', '-1 month')           AS month_earlier,   -- 2026-01-14
    DATE('2026-02-14', 'start of month')     AS month_start,     -- 2026-02-01
    DATE('2026-02-14', 'start of month', '+1 month', '-1 day') AS month_end,  -- 2026-02-28
    DATE('2026-02-14', 'start of year')      AS year_start;      -- 2026-01-01

1 row

0 ms

That month_end chain is the standard way to get the last day of any month without hard-coding 28/30/31 or worrying about leap years.

Date arithmetic — age and gaps

Subtracting dates needs JULIANDAY, which converts a date to a plain number of days.

SELECT
    first_name,
    birth_date,
    CAST((JULIANDAY('2026-09-01') - JULIANDAY(birth_date)) / 365.25 AS INTEGER) AS age
FROM students
ORDER BY age DESC;

… first 15 of 300 rows

300 rows

0 ms

:::gotcha ⚠️ Why 365.25 and why CAST, not ROUND 365.25 accounts for leap years. CAST(... AS INTEGER) truncates, which is what "age" means — you are 21 until the day you turn 22, not "rounded to 22" six months early. Using ROUND here makes half your students a year older than they are. Note also that the reference date is written literally rather than as DATE('now'), so the numbers in this table stay reproducible for your class. :::

Gaps between events, combining JULIANDAY with the LAG from Chapter 7:

SELECT
    student_id,
    enrolled_at,
    LAG(enrolled_at) OVER (PARTITION BY student_id ORDER BY enrolled_at) AS previous,
    CAST(JULIANDAY(enrolled_at)
       - JULIANDAY(LAG(enrolled_at) OVER (PARTITION BY student_id ORDER BY enrolled_at))
      AS INTEGER) AS days_since_last
FROM enrollments
WHERE student_id IN (1, 3)
ORDER BY student_id, enrolled_at;

… first 15 of 16 rows

16 rows

0 ms

student_idenrolled_atpreviousdays_since_last

12025-09-05NULLNULL 12025-09-052025-09-050 12026-02-032025-09-05151 32025-09-18NULLNULL 32026-02-052025-09-18140 32026-02-052026-02-050

CAST and type conversion

SELECT
    CAST('42'   AS INTEGER)   AS text_to_int,      -- 42
    CAST(3.99   AS INTEGER)   AS float_to_int,     -- 3   (truncated, not rounded!)
    CAST(7      AS REAL)      AS int_to_real,      -- 7.0
    CAST(42     AS TEXT)      AS int_to_text,      -- '42'
    CAST('abc'  AS INTEGER)   AS bad_cast;         -- 0 in SQLite, ERROR in PostgreSQL

1 row

0 ms

:::gotcha ⚠️ CAST to INTEGER truncates; failed casts differ by database CAST(3.99 AS INTEGER) is 3, not 4. Use ROUND when you mean rounding. And a bad cast is silent in SQLite (returns 0) but raises an error in PostgreSQL — so a data-cleaning query that "works" locally can fail in production, or worse, quietly turn every unparseable value into a zero. :::

The most common practical use is the one from Chapter 2 — forcing real division:

SELECT 7 / 2                    AS integer_division,   -- 3
       7 * 1.0 / 2              AS trick_multiply,     -- 3.5
       CAST(7 AS REAL) / 2      AS explicit_cast;      -- 3.5

1 row

0 ms

Math and rounding

FunctionDoesExample → result

ROUND(x, n)Round to n decimalsROUND(79.428, 2) → 79.43 ABS(x)Absolute valueABS(-15) → 15 x % yRemainder17 % 5 → 2 MIN(a,b) / MAX(a,b)Smaller / larger of the argumentsMAX(10, 20) → 20 ABS, POWER, SQRTStandard mathsSQRT(16) → 4.0

:::gotcha ⚠️ MIN means two different things MIN(grade) with one argument is the aggregate — the lowest grade across many rows. MIN(a, b) with two arguments is a scalar function comparing two values in the same row. Same name, completely different behaviour. (PostgreSQL avoids the confusion by calling the scalar version LEAST/GREATEST.) :::

Practice set

-- 1. Letter grade for every graded enrollment
SELECT enrollment_id, grade,
       CASE WHEN grade >= 90 THEN 'A' WHEN grade >= 80 THEN 'B'
            WHEN grade >= 70 THEN 'C' WHEN grade >= 60 THEN 'D'
            ELSE 'F' END AS letter
FROM enrollments WHERE grade IS NOT NULL ORDER BY grade DESC;

-- 2. How many of each letter grade?
SELECT CASE WHEN grade >= 90 THEN 'A' WHEN grade >= 80 THEN 'B'
            WHEN grade >= 70 THEN 'C' WHEN grade >= 60 THEN 'D'
            ELSE 'F' END AS letter,
       COUNT(*) AS n
FROM enrollments WHERE grade IS NOT NULL GROUP BY letter ORDER BY letter;

-- 3. Status pivot per course
SELECT course_id,
       SUM(CASE WHEN status='completed' THEN 1 ELSE 0 END) AS completed,
       SUM(CASE WHEN status='active'    THEN 1 ELSE 0 END) AS active,
       SUM(CASE WHEN status='failed'    THEN 1 ELSE 0 END) AS failed
FROM enrollments GROUP BY course_id;

-- 4. Generated email addresses
SELECT LOWER(first_name) || '.' || LOWER(last_name) || '@school.uz' AS email
FROM students;

-- 5. Age of every student
SELECT first_name,
       CAST((JULIANDAY('2026-09-01') - JULIANDAY(birth_date))/365.25 AS INTEGER) AS age
FROM students ORDER BY age DESC;

-- 6. Price band per course
SELECT title, price,
       CASE WHEN price >= 2500000 THEN 'premium'
            WHEN price >= 1400000 THEN 'standard'
            ELSE 'basic' END AS price_band
FROM courses ORDER BY price DESC;

-- 7. Enrollments per weekday
SELECT strftime('%w', enrolled_at) AS weekday_num, COUNT(*) AS n
FROM enrollments GROUP BY weekday_num ORDER BY weekday_num;

-- 8. Students grouped into age bands
SELECT CASE WHEN CAST((JULIANDAY('2026-09-01')-JULIANDAY(birth_date))/365.25 AS INTEGER) <= 21
            THEN '21 and under' ELSE '22 and over' END AS age_band,
       COUNT(*) AS n
FROM students GROUP BY age_band;

Result 1

… first 15 of 1826 rows

1826 rows

Result 2

5 rows

Result 3

14 rows

Result 4

… first 15 of 300 rows

300 rows

Result 5

… first 15 of 300 rows

300 rows

Result 6

15 rows

Result 7

7 rows

Result 8

2 rows

5 ms

Interview questions

Q: How does CASE decide which branch to use?

It evaluates conditions top to bottom and returns the first that is TRUE, so order matters. If nothing matches and there is no ELSE, it returns NULL.

Q: How do you build a pivot table in plain SQL?

SUM(CASE WHEN col = 'value' THEN 1 ELSE 0 END) AS value, one such expression per output column, with GROUP BY on the row dimension.

Q: Why is ELSE 0 wrong inside AVG(CASE ...)?

Because it converts non-matching rows into zeros that enter the average, instead of being excluded. Omit the ELSE so they become NULL and AVG skips them.

Q: Difference between simple and searched CASE?

Simple CASE expr WHEN v THEN ... only tests equality against one expression and cannot match NULL. Searched CASE WHEN condition THEN ... allows any condition, including IS NULL.

Q: Is SUBSTR 0-based or 1-based?

1-based. The first character is position 1.

Q: How do you get the last day of a month?

Go to the start of the month, add a month, subtract a day: DATE(d,'start of month','+1 month','-1 day'). Never hard-code 30 or 31.

Q: What is wrong with WHERE UPPER(name) = 'ALI' on a large table?

The function on the column makes it non-sargable, so no index can be used. Fix the data or add a functional index rather than wrapping the column at query time.

Q: CAST(3.99 AS INTEGER) — 3 or 4?

1. CAST truncates. Use ROUND if you want 4.

Summary

• CASE WHEN is SQL's if/else. Conditions are checked in order, so write them high-to-low and put the NULL check first — otherwise ungraded rows silently become F.
• Simple CASE cannot match NULL; the searched form can.
• CASE in ORDER BY gives custom sort orders and controls NULL placement.
• SUM(CASE WHEN ... THEN 1 ELSE 0 END) is the pivot idiom — rows become columns, with real zeros. Drop the ELSE 0 when the aggregate is AVG.
• String work: ||, UPPER/LOWER, SUBSTR (1-based), TRIM, REPLACE, LENGTH, INSTR. Names differ a lot between databases.
• TRIM + LOWER fixes most dirty text, but cleaning inside WHERE costs the index — clean the stored data instead.
• Dates: strftime to extract, DATE(d, modifier) to shift, JULIANDAY to subtract. 'start of month','+1 month','-1 day' gives month-end safely.
• Age = CAST(days / 365.25 AS INTEGER) — truncate, never round.
• CAST to INTEGER truncates; a bad cast is silently 0 in SQLite but an error in PostgreSQL.

Next: Chapter 9 — stacking query results on top of each other with UNION, INTERSECT and EXCEPT.