Recipes

Learn SQL recipes with practical examples and guided drills on dbSyntax.

Recipes

Task-phrased pages for the questions people actually Google: "extract year from date," "difference between two dates," "convert string to da...

Extract year, month, or day from a date

Short answer: EXTRACT(YEAR FROM date_col). The same function works for MONTH, DAY, HOUR, MINUTE, SECOND, DOW (day of week), DOY (day of year...

Difference between two dates

Short answer: in Postgres / DuckDB / Oracle, subtract: end_date - start_date returns days. In MySQL / SQL Server / Snowflake / Redshift, use...

Convert string to date (and date to string)

Short answer: if the string is in ISO format ('2026-04-26'), CAST(s AS DATE) works on every engine. For non-ISO formats, use TO_DATE (Postgr...

First day and last day of month

Short answer: for the first day of a month, DATE_TRUNC('month', date_col) works on Postgres / DuckDB / Snowflake / Redshift / Databricks. Bi...

Current date, time, and timestamp

Short answer: CURRENT_DATE for today's date, CURRENT_TIMESTAMP for the current date-and-time. Both are ANSI standard and work without parent...

Extract a substring from a string

Short answer: SUBSTRING(str FROM start FOR length) is the ANSI standard. Most engines also accept the function-call form SUBSTRING(str, star...

Split a string by a delimiter

Short answer: to grab one piece, use SPLIT_PART(str, delim, n) (Postgres / Snowflake / Redshift / DuckDB) or SUBSTRING_INDEX(str, delim, n) ...

Regex in SQL — match, replace, extract

Short answer: REGEXP_REPLACE(str, pattern, replacement) to substitute, REGEXP_LIKE(str, pattern) (or str ~ pattern in Postgres) to match, RE...

Concatenate strings

Short answer: || is the ANSI standard concatenation operator (Postgres / DuckDB / SQLite / Oracle / Snowflake / Redshift / BigQuery). CONCAT...

Concatenate rows into a single string

Short answer: the function names differ across engines but the operation is the same: collapse a column from many rows into a single delimit...

Find duplicate rows

Short answer: SELECT col, COUNT() FROM t GROUP BY col HAVING COUNT() > 1 finds duplicates by a single column. For "rows that match on multip...

Delete duplicate rows

Short answer: rank the rows with ROW_NUMBER() OVER (PARTITION BY <dup_columns> ORDER BY <preferred_first>), then delete every row with rank ...

Rows to columns (pivot)

Short answer: turn distinct values from a column into multiple columns. Two flavors. The portable form: SUM(CASE WHEN col = 'value' THEN 1 E...

Columns to rows (unpivot)

Short answer: turn many columns into rows. The portable form: UNION ALL a SELECT per column, each tagged with a category label. Modern engin...

Round a number to N decimal places

Short answer: ROUND(value, n) rounds to n decimal places, and works on every major engine. CEIL(value) rounds up to the next integer; FLOOR(...

Calculate a percentage

Short answer: (part / whole) 100. The two real complications: (1) integer division silently truncates to zero, so cast to DECIMAL or FLOAT f...

Modulo and remainder

Short answer: MOD(a, b) returns the remainder of a / b on most engines. Postgres / MySQL / DuckDB / SQL Server / SQLite also accept the % op...

Running total (cumulative sum)

Short answer: SUM(value) OVER (ORDER BY time_col) accumulates a value across rows in time order. Adding PARTITION BY resets the running tota...

Previous row value (LAG / LEAD)

Short answer: LAG(value) OVER (PARTITION BY group ORDER BY time_col) returns the value from the previous row within the partition. LEAD retu...

First or last row per group

Short answer: ROW_NUMBER() OVER (PARTITION BY group ORDER BY time_col) ranks rows within each group; filter to = 1 for the first row, or use...

Rank rows within a group

Short answer: three ranking functions, three different tie-handling behaviors. ROW_NUMBER gives every row a unique rank (ties broken arbitra...

Percentile and median

Short answer: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) returns the median (50th percentile, interpolated). PERCENTILE_DISC(0.5) re...

Replace NULL with 0 (or any default)

Short answer: COALESCE(col, 0) returns the first non-NULL of its arguments. It is the canonical, ANSI-portable way to substitute a default f...

Ignore NULLs in aggregates and window functions

Short answer: standard SQL aggregates (SUM, AVG, MIN, MAX, COUNT(col)) already ignore NULLs by default. The function applies only to non-NUL...

NULL comparison: why = NULL never works

Short answer: col = NULL doesn't work — NULL is not a value, it's the absence of a value, and any comparison against NULL returns NULL (whic...

Convert a string to a number

Short answer: CAST(s AS INTEGER) (or DECIMAL, FLOAT) on every engine. Postgres / DuckDB / Snowflake also accept s::INTEGER. TRY_CAST (most e...

Convert a number to a string

Short answer: CAST(n AS VARCHAR) (or TEXT, STRING) on every engine. For padding, decimals, or currency formatting: TO_CHAR(n, format) (Postg...

ORDER BY with NULLs first or last

Short answer: ORDER BY col NULLS LAST (or NULLS FIRST) on Postgres / Oracle / DuckDB / Snowflake / Redshift / SQLite / BigQuery. MySQL and S...

ORDER BY random: pick random rows

Short answer: ORDER BY RANDOM() (Postgres / DuckDB / SQLite / Snowflake) or ORDER BY RAND() (MySQL / BigQuery) or ORDER BY NEWID() (SQL Serv...

Custom sort order with CASE

Short answer: ORDER BY CASE col WHEN 'high' THEN 1 WHEN 'medium' THEN 2 WHEN 'low' THEN 3 END lets you define an arbitrary sort order for no...

Generate a sequence of numbers

Short answer: generate_series(start, stop) on Postgres / DuckDB returns one row per integer in the range. GENERATE_ARRAY(start, stop) on Big...

Generate dates between two dates (date spine)

Short answer: generate_series(start_date, end_date, INTERVAL '1 day') on Postgres / DuckDB. GENERATE_DATE_ARRAY(start, end, INTERVAL 1 DAY) ...

CASE with multiple conditions

Short answer: CASE has two forms: simple (matches one column to literals) and searched (full predicates per branch). Use the searched form f...