SQL ships with dozens of built-in functions that turn raw columns into totals, formatted text, clean dates, and safe fallbacks. This is a categorized reference of the functions you will actually use, with a runnable example and the exact result for each one, plus the cross-database name differences that trip people up when they move between MySQL, PostgreSQL, SQL Server, and Oracle.
What this guide covers
How to read this reference
A SQL function takes zero or more inputs and returns a single value. The two big families behave differently and it is worth keeping them straight before you dive into the tables below.
- Scalar functions run once per row and return one value per row. UPPER, ROUND, and DATE_FORMAT are scalar - a 100-row query gets 100 results back.
- Aggregate functions collapse many rows into one value. COUNT, SUM, and AVG are aggregates - a 100-row query might return a single number, or one number per group when you add
GROUP BY.
Every function here works inside SELECT, but scalar functions also work in WHERE, ORDER BY, and UPDATE. Aggregates are restricted to SELECT and HAVING. If you are still learning where each clause fits, the DQL commands guide and the SQL syntax guide cover the surrounding grammar. For the shortest possible lookup, the SQL cheat sheet is a one-page companion to this page.
Dialect note: every example was checked against MySQL 8 unless labelled otherwise. Where a function has a different name in PostgreSQL, SQL Server, or Oracle, the difference is called out inline and summarised in the cross-database table near the end.
Aggregate functions
Aggregate functions summarise a set of rows into one value. Without GROUP BY they fold the whole result set into a single row; with GROUP BY they return one row per group. The single most important rule: every aggregate except COUNT(*) ignores NULLs. That one fact explains most surprising averages and counts.
| Function | What it does | Example | Result |
|---|---|---|---|
| COUNT | Counts rows; COUNT(*) counts all, COUNT(col) skips NULLs | COUNT(*) | 1000 |
| SUM | Adds numeric values, ignoring NULLs | SUM(amount) | 48250.00 |
| AVG | Mean of non-NULL values | AVG(price) | 19.99 |
| MIN | Smallest value (works on numbers, text, dates) | MIN(created_at) | 2021-03-01 |
| MAX | Largest value | MAX(price) | 499.00 |
| GROUP_CONCAT | Joins values from a group into one string (MySQL) | GROUP_CONCAT(tag) | sql,index,tuning |
| STRING_AGG | Same idea in PostgreSQL / SQL Server | STRING_AGG(tag, ',') | sql,index,tuning |
COUNT: the two forms are not the same
COUNT(*) counts every row. COUNT(column) counts only rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-NULL values. Mixing these up quietly changes your numbers.
-- Sample: 5 orders, 2 have a NULL coupon_code
SELECT
COUNT(*) AS all_rows,
COUNT(coupon_code) AS with_coupon,
COUNT(DISTINCT coupon_code) AS unique_coupons
FROM orders;all_rows | with_coupon | unique_coupons
5 | 3 | 2SUM and AVG treat NULL as absent, not zero
This is the classic gotcha. AVG divides the total by the count of non-NULL values, so a NULL is not the same as a zero.
-- scores: 10, 20, NULL
SELECT SUM(score) AS total,
AVG(score) AS avg_ignoring_null,
AVG(COALESCE(score, 0)) AS avg_null_as_zero
FROM tests;total | avg_ignoring_null | avg_null_as_zero
30 | 15.00 | 10.00DISTINCT inside an aggregate
Every aggregate accepts DISTINCT to deduplicate before it does its work. SUM(DISTINCT x) adds each distinct value once, and AVG(DISTINCT x) averages the distinct set.
-- amounts: 100, 100, 200
SELECT SUM(amount) AS plain, -- 400
SUM(DISTINCT amount) AS distinct_sum -- 300
FROM payments;A frequent real-world use of COUNT(DISTINCT ...) is measuring cardinality - how many unique customers placed an order, how many distinct products sold on a given day. Because the database must track every distinct value it has seen, COUNT(DISTINCT) is more expensive than a plain count on large tables, so reserve it for when you genuinely need uniqueness.
GROUP BY and HAVING
Aggregates come alive with GROUP BY, which produces one aggregated row per group, and HAVING, which filters those groups after aggregation (unlike WHERE, which filters rows before). A common report is "categories whose total sales exceed a threshold".
SELECT category,
COUNT(*) AS products,
SUM(revenue) AS total_rev,
ROUND(AVG(revenue), 2) AS avg_rev
FROM sales
GROUP BY category
HAVING SUM(revenue) > 10000
ORDER BY total_rev DESC;category | products | total_rev | avg_rev
Hardware | 48 | 91200.00 | 1900.00
Software | 31 | 40300.00 | 1300.00Remember the ordering: WHERE filters individual rows, then GROUP BY forms groups, then HAVING filters the groups, then ORDER BY sorts the final output. Putting an aggregate in WHERE is a syntax error - that is what HAVING is for.
GROUP_CONCAT vs STRING_AGG
To flatten a group into a delimited string, MySQL uses GROUP_CONCAT while PostgreSQL and SQL Server use STRING_AGG. Both support ordering and a separator; the syntax differs.
SELECT user_id,
GROUP_CONCAT(tag ORDER BY tag SEPARATOR ', ') AS tags
FROM user_tags
GROUP BY user_id;SELECT user_id,
STRING_AGG(tag, ', ' ORDER BY tag) AS tags
FROM user_tags
GROUP BY user_id;Watch out: GROUP_CONCAT silently truncates at group_concat_max_len (default 1024 bytes in MySQL). For long lists raise it with SET SESSION group_concat_max_len = 1000000; or you will lose values with no error.
String functions
String functions clean, combine, measure, and reshape text. They are scalar, so they run per row. The most common real-world use is normalising user input (trimming, upper/lower-casing) and building display strings.
| Function | What it does | Example | Result |
|---|---|---|---|
| CONCAT | Joins strings together | CONCAT('a','b','c') | abc |
| CONCAT_WS | Joins with a separator, skipping NULLs | CONCAT_WS('-','2024','01') | 2024-01 |
| LENGTH | Length in bytes (MySQL) | LENGTH('cafe') | 4 |
| CHAR_LENGTH | Length in characters | CHAR_LENGTH('cafe') | 4 |
| SUBSTRING | Extracts part of a string (1-indexed) | SUBSTRING('database',1,4) | data |
| LEFT | Leftmost n characters | LEFT('database',4) | data |
| RIGHT | Rightmost n characters | RIGHT('database',4) | base |
| UPPER | Upper-cases text | UPPER('sql') | SQL |
| LOWER | Lower-cases text | LOWER('SQL') | sql |
| TRIM | Removes leading & trailing spaces | TRIM(' hi ') | hi |
| LTRIM | Removes leading spaces | LTRIM(' hi') | hi |
| RTRIM | Removes trailing spaces | RTRIM('hi ') | hi |
| REPLACE | Swaps every occurrence of a substring | REPLACE('a.b.c','.','-') | a-b-c |
| LPAD | Left-pads to a target length | LPAD('7',3,'0') | 007 |
| RPAD | Right-pads to a target length | RPAD('7',3,'0') | 700 |
| INSTR | Position of a substring (0 if absent) | INSTR('email@x','@') | 6 |
| POSITION | ANSI form of INSTR | POSITION('@' IN 'a@b') | 2 |
| REVERSE | Reverses the characters | REVERSE('abc') | cba |
CONCAT and CONCAT_WS
CONCAT glues arguments together. Its NULL behaviour differs by engine: in MySQL CONCAT returns the joined string treating no argument specially, but a single NULL argument yields NULL in standard SQL and in PostgreSQL. CONCAT_WS ("with separator") is safer for display because it drops NULL arguments instead of poisoning the whole result.
SELECT
CONCAT(first_name, ' ', last_name) AS full_name,
CONCAT_WS(', ', city, state, country) AS location
FROM customers
WHERE id = 42;full_name | location
Ada Lovelace | London, England, UKIf state were NULL, CONCAT_WS would return London, UK with no stray separator - that is the whole reason it exists.
SUBSTRING, LEFT, and RIGHT
SUBSTRING(str, start, length) is 1-indexed. Omit the length to take everything from start to the end. LEFT and RIGHT are shortcuts for the common edge cases.
SELECT
SUBSTRING('2024-11-03', 6, 2) AS month_part, -- 11
LEFT('2024-11-03', 4) AS year_part, -- 2024
SUBSTRING('orders_2024', 8) AS tail; -- 2024LENGTH vs CHAR_LENGTH
For ASCII text they agree. For multi-byte characters they diverge: LENGTH counts bytes, CHAR_LENGTH counts characters. Use CHAR_LENGTH whenever you mean "how many characters".
SELECT LENGTH('nino') AS bytes, -- 4 (with an accented n: 5)
CHAR_LENGTH('nino') AS characters; -- 4In SQL Server the character-count function is LEN (which also ignores trailing spaces); PostgreSQL and Oracle use LENGTH to mean characters, not bytes. This is a frequent portability snag.
Padding and trimming for clean output
-- Zero-pad an invoice number to 6 digits
SELECT LPAD(CAST(id AS CHAR), 6, '0') AS invoice_no
FROM invoices;
-- 42 becomes 000042Always TRIM free-text input before you store or compare it. A trailing space is invisible in a report but makes 'admin ' and 'admin' unequal, which breaks joins and lookups in ways that are painful to debug. Normalising case with LOWER at write time (or comparing with LOWER() on both sides) avoids the same class of bug for email addresses and usernames.
Finding and replacing inside strings
INSTR tells you where a substring starts (or 0 if it is not present), and REPLACE swaps every occurrence. Together they cover most "clean this text up" tasks without a regular expression.
-- Split an email into local part and domain
SELECT
LEFT(email, INSTR(email, '@') - 1) AS local_part,
SUBSTRING(email, INSTR(email, '@') + 1) AS domain
FROM users;local_part | domain
ada | example.comNumeric & math functions
Numeric functions handle rounding, remainders, powers, and random values. The rounding family (ROUND, CEIL, FLOOR, TRUNCATE) is where most subtle bugs live, because each one breaks ties and negatives differently.
| Function | What it does | Example | Result |
|---|---|---|---|
| ROUND | Rounds to n decimals (half away from zero) | ROUND(2.345, 2) | 2.35 |
| CEIL | Rounds up to next integer | CEIL(2.1) | 3 |
| FLOOR | Rounds down to previous integer | FLOOR(2.9) | 2 |
| ABS | Absolute value | ABS(-15) | 15 |
| MOD | Remainder of division | MOD(17, 5) | 2 |
| POWER | Raises to a power | POWER(2, 10) | 1024 |
| SQRT | Square root | SQRT(144) | 12 |
| TRUNCATE | Cuts decimals without rounding | TRUNCATE(2.789, 1) | 2.7 |
| SIGN | Returns -1, 0, or 1 | SIGN(-42) | -1 |
| RAND | Random float 0 ≤ x < 1 | RAND() | 0.5721... |
ROUND vs TRUNCATE vs FLOOR
All three shorten a decimal, but they do it differently. ROUND looks at the next digit and rounds; TRUNCATE just chops; FLOOR always goes toward negative infinity.
SELECT
ROUND(2.678, 2) AS rounded, -- 2.68
TRUNCATE(2.678, 2) AS truncated, -- 2.67
FLOOR(-2.1) AS floored, -- -3
CEIL(-2.1) AS ceiled; -- -2Note how FLOOR(-2.1) is -3, not -2: flooring rounds down, which for negatives means away from zero. In SQL Server, CEIL is spelled CEILING, and there is no TRUNCATE function - you use ROUND(x, n, 1) where the third argument 1 switches to truncation.
MOD for buckets and alternating rows
-- Split rows into 4 shards by id
SELECT id, MOD(id, 4) AS shard
FROM events;
-- id 10 -> shard 2, id 11 -> shard 3MOD(a, b) and the operator a % b are equivalent in MySQL and SQL Server. PostgreSQL and Oracle prefer the MOD function. A classic use is testing parity: MOD(n, 2) = 0 identifies even numbers, which is handy for alternating-row logic or splitting a workload across two workers.
POWER, SQRT, ABS, and SIGN
These round out the math toolkit. POWER(base, exp) raises to a power, SQRT takes a square root, ABS drops the sign, and SIGN reports it as -1, 0, or 1. SIGN is useful for bucketing values into "up, flat, down" without a CASE.
SELECT
POWER(10, 3) AS thousand, -- 1000
SQRT(2) AS root_two, -- 1.4142...
ABS(target - actual) AS deviation,
SIGN(target - actual) AS direction -- -1, 0, or 1
FROM forecasts;RAND for sampling
-- Pull ~10 random rows (small tables only)
SELECT * FROM quotes
ORDER BY RAND()
LIMIT 10;Watch out: ORDER BY RAND() scores and sorts every row, so it is fine for a few thousand rows but very slow on large tables. For big tables sample on an indexed key instead. PostgreSQL spells the function RANDOM().
Date & time functions
Date handling is where dialects diverge the most. The concepts are shared - get the current time, pull out a part, add an interval, format for display - but the function names change a lot between engines.
| Function | What it does | Example | Result |
|---|---|---|---|
| NOW | Current date & time | NOW() | 2026-07-04 10:15:00 |
| CURRENT_TIMESTAMP | ANSI synonym for NOW | CURRENT_TIMESTAMP | 2026-07-04 10:15:00 |
| CURDATE | Current date, no time | CURDATE() | 2026-07-04 |
| DATE | Strips the time part | DATE('2026-07-04 10:15') | 2026-07-04 |
| EXTRACT | Pulls one field out of a date | EXTRACT(YEAR FROM d) | 2026 |
| DATE_ADD | Adds an interval | DATE_ADD(d, INTERVAL 7 DAY) | 2026-07-11 |
| DATE_SUB | Subtracts an interval | DATE_SUB(d, INTERVAL 1 MONTH) | 2026-06-04 |
| DATEDIFF | Days between two dates | DATEDIFF('2026-07-11', d) | 7 |
| DATE_FORMAT | Formats a date as text (MySQL) | DATE_FORMAT(d, '%Y/%m') | 2026/07 |
| TO_CHAR | Formats a date (Postgres/Oracle) | TO_CHAR(d, 'YYYY/MM') | 2026/07 |
| DAY | Day of month | DAY('2026-07-04') | 4 |
| MONTH | Month number | MONTH('2026-07-04') | 7 |
| YEAR | Year number | YEAR('2026-07-04') | 2026 |
| DAYNAME | Weekday name | DAYNAME('2026-07-04') | Saturday |
Getting "now" and today
SELECT
NOW() AS full_ts, -- 2026-07-04 10:15:00
CURDATE() AS today, -- 2026-07-04
CURRENT_TIMESTAMP AS ansi_now;PostgreSQL and Oracle use CURRENT_DATE (no parentheses) instead of CURDATE(). SQL Server uses GETDATE() or SYSDATETIME() for the timestamp.
EXTRACT and the DAY / MONTH / YEAR shortcuts
EXTRACT is the portable, ANSI way to pull one component from a date. MySQL also offers the shorthand functions DAY(), MONTH(), and YEAR().
SELECT
EXTRACT(YEAR FROM created_at) AS yr,
MONTH(created_at) AS mo,
DAYNAME(created_at) AS weekday
FROM orders
WHERE id = 100;yr | mo | weekday
2026 | 7 | SaturdayDate arithmetic
Use DATE_ADD / DATE_SUB with an INTERVAL to shift dates, and DATEDIFF to measure the gap.
-- Orders placed in the last 30 days
SELECT * FROM orders
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
-- Age of an account in days
SELECT DATEDIFF(CURDATE(), signup_date) AS days_active
FROM users;Watch out: DATEDIFF has different argument orders and return units by engine. MySQL returns days as DATEDIFF(end, start). SQL Server is DATEDIFF(unit, start, end) and can return any unit, e.g. DATEDIFF(DAY, start, end). Do not assume the two are interchangeable.
Formatting: DATE_FORMAT vs TO_CHAR
MySQL formats dates with DATE_FORMAT using % tokens; PostgreSQL and Oracle use TO_CHAR with pattern letters like YYYY and MM.
SELECT DATE_FORMAT(created_at, '%W, %M %e, %Y') AS pretty
FROM orders;
-- Saturday, July 4, 2026SELECT TO_CHAR(created_at, 'FMDay, FMMonth DD, YYYY') AS pretty
FROM orders;The token vocabularies are entirely different, so a format string is never portable. In MySQL, %Y is a four-digit year, %m is a zero-padded month, and %W is the full weekday name. In TO_CHAR, those are YYYY, MM, and Day. When you generate SQL from application code that must support several engines, keep the formatting out of SQL entirely and format the raw date in the application layer instead. It saves you from maintaining two sets of token strings.
Truncating timestamps to a period
Reporting queries frequently need to group by day, month, or hour. In MySQL, DATE() drops the time and DATE_FORMAT can floor to any period; PostgreSQL has the dedicated DATE_TRUNC('month', d).
-- Monthly signups (MySQL)
SELECT DATE_FORMAT(signup_date, '%Y-%m-01') AS month,
COUNT(*) AS signups
FROM users
GROUP BY DATE_FORMAT(signup_date, '%Y-%m-01')
ORDER BY month;Conditional & NULL functions
These functions let a query make decisions and supply fallbacks. They are the workhorses for turning NULLs into sensible defaults and for inline branching without leaving SQL.
| Function | What it does | Example | Result |
|---|---|---|---|
| CASE | Multi-branch conditional expression | CASE WHEN x>0 THEN 'pos' ELSE 'neg' END | pos |
| COALESCE | First non-NULL of a list (ANSI) | COALESCE(NULL, NULL, 'x') | x |
| NULLIF | Returns NULL if two args are equal | NULLIF(5, 5) | NULL |
| IFNULL | Fallback if first arg is NULL (MySQL) | IFNULL(NULL, 0) | 0 |
| ISNULL | Fallback if NULL (SQL Server) | ISNULL(NULL, 0) | 0 |
| NVL | Fallback if NULL (Oracle) | NVL(NULL, 0) | 0 |
| GREATEST | Largest of the arguments | GREATEST(3, 9, 5) | 9 |
| LEAST | Smallest of the arguments | LEAST(3, 9, 5) | 3 |
CASE: searched and simple forms
CASE is the most flexible conditional. The searched form tests any boolean; the simple form compares one expression to values.
-- Searched CASE: any condition
SELECT name,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'F'
END AS grade
FROM students;-- Simple CASE: compare one value
SELECT id,
CASE status
WHEN 1 THEN 'active'
WHEN 0 THEN 'inactive'
ELSE 'unknown'
END AS label
FROM accounts;A common trick is conditional aggregation - putting CASE inside SUM to count rows that match a condition:
SELECT
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count,
SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded_total
FROM orders;COALESCE, IFNULL, ISNULL, NVL
All of these supply a fallback for NULL. COALESCE is the portable ANSI standard and accepts any number of arguments, returning the first non-NULL. The two-argument variants are engine-specific.
-- Portable: works everywhere
SELECT COALESCE(nickname, first_name, 'Guest') AS display_name
FROM users;The two-argument shortcuts map like this: MySQL IFNULL(x, y), SQL Server ISNULL(x, y), Oracle NVL(x, y). They all mean "if x is NULL, use y". Prefer COALESCE unless you specifically need one engine's version, and you never have to remember which name goes where.
Careful with types: SQL Server's ISNULL forces the result to the type of the first argument, which can truncate the fallback. COALESCE uses standard type precedence and is safer. That alone is a good reason to default to COALESCE.
NULLIF for safe division
NULLIF(a, b) returns NULL when a = b. Its most useful pattern is dodging divide-by-zero: turn a zero denominator into NULL so the division yields NULL instead of erroring.
SELECT revenue / NULLIF(orders, 0) AS avg_order_value
FROM daily_stats;
-- orders = 0 -> result is NULL, not a division errorYou can pair it with a fallback to show a clean zero instead of NULL: COALESCE(revenue / NULLIF(orders, 0), 0). This idiom - NULLIF on the denominator, COALESCE on the outside - is worth memorising because divide-by-zero is one of the most common runtime errors in reporting SQL.
GREATEST and LEAST
GREATEST and LEAST compare their arguments horizontally, across columns in the same row, rather than down a column like MAX and MIN do. That distinction confuses people: MAX(a) is an aggregate over rows, while GREATEST(a, b, c) picks the largest of three values in one row.
SELECT product,
GREATEST(price_us, price_eu, price_uk) AS highest_price,
LEAST(price_us, price_eu, price_uk) AS lowest_price
FROM catalog;Both return NULL if any argument is NULL in MySQL, so wrap suspect columns in COALESCE first if a NULL should not poison the comparison.
Conversion & CAST
Type conversion turns a value of one type into another - a string to a date, a number to text, a decimal to an integer. The two standard tools are CAST and CONVERT.
| Function | What it does | Example | Result |
|---|---|---|---|
| CAST | ANSI conversion, CAST(x AS type) | CAST('2026-07-04' AS DATE) | 2026-07-04 |
| CAST | Number to string | CAST(42 AS CHAR) | 42 |
| CAST | Decimal with precision/scale | CAST(3.14159 AS DECIMAL(4,2)) | 3.14 |
| CONVERT | MySQL: same as CAST, alternate syntax | CONVERT('99', SIGNED) | 99 |
| CONVERT | SQL Server: adds a style code for dates | CONVERT(VARCHAR, d, 23) | 2026-07-04 |
CAST is the portable choice
CAST(expr AS type) is ANSI standard and works the same across MySQL, PostgreSQL, SQL Server, and Oracle. Reach for it first.
SELECT
CAST('2026-07-04' AS DATE) AS as_date,
CAST(price AS DECIMAL(10,2)) AS as_money,
CAST(id AS CHAR) AS as_text
FROM products;PostgreSQL also offers the shorthand value::type, e.g. '42'::int, which is handy but not portable. Keep it out of code that might move engines. When converting text to a number or date, be strict about the input format: a stray thousands separator or a non-ISO date string will either error or, worse, silently produce a wrong value depending on the engine's leniency. Validate and clean the string with the string functions above before you cast it.
CONVERT and implicit conversion
CONVERT does the same job with different syntax per engine. In MySQL it is CONVERT(expr, type); in SQL Server it is CONVERT(type, expr, style) where style controls date formatting. Databases also convert types implicitly when they can, but relying on that is risky.
Watch out: comparing an indexed column to the wrong type forces an implicit conversion that can disable the index and scan the whole table. Comparing a numeric column to '42' (a string) is the classic offender. Cast explicitly and match types on both sides - the performance-sensitive query patterns depend on it.
Window functions (pointer)
Window functions compute across a set of rows related to the current row without collapsing them into a group. They are the tool for running totals, rankings, and row-to-row comparisons. Because they are a large topic on their own, here is just the roster - the full mechanics, frames, and PARTITION BY details live in the dedicated SQL window functions guide.
| Function | What it does | Example | Result idea |
|---|---|---|---|
| ROW_NUMBER | Sequential number per partition | ROW_NUMBER() OVER (ORDER BY score DESC) | 1, 2, 3, ... |
| RANK | Rank with gaps after ties | RANK() OVER (ORDER BY score DESC) | 1, 1, 3 |
| DENSE_RANK | Rank with no gaps after ties | DENSE_RANK() OVER (...) | 1, 1, 2 |
| LAG | Value from a previous row | LAG(amount) OVER (ORDER BY d) | prior row's amount |
| LEAD | Value from a following row | LEAD(amount) OVER (ORDER BY d) | next row's amount |
| NTILE | Splits rows into n buckets | NTILE(4) OVER (ORDER BY score) | 1..4 quartiles |
-- Rank employees by salary within each department
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;Window functions are supported in MySQL 8+, PostgreSQL, SQL Server, and Oracle. If you are on MySQL 5.7 or older they are unavailable and you emulate them with self-joins or variables. The key mental model is that the query still returns every input row - the window function just adds a computed column that "sees" a window of neighbouring rows defined by the OVER clause. That is the crucial difference from a GROUP BY aggregate, which throws the detail rows away. For the deep dive, head to the window functions guide and the advanced SQL course.
Cross-database name differences
The single biggest source of "it worked on my machine" bugs with functions is name and signature drift between engines. This table collects the ones that bite most often. When in doubt, prefer the ANSI-standard column.
| Purpose | MySQL | PostgreSQL | SQL Server | Oracle |
|---|---|---|---|---|
| NULL fallback | IFNULL / COALESCE | COALESCE | ISNULL / COALESCE | NVL / COALESCE |
| Format a date | DATE_FORMAT | TO_CHAR | FORMAT / CONVERT | TO_CHAR |
| Current timestamp | NOW() | NOW() | GETDATE() | SYSDATE |
| Today's date | CURDATE() | CURRENT_DATE | CAST(GETDATE() AS DATE) | TRUNC(SYSDATE) |
| Char length | CHAR_LENGTH | LENGTH | LEN | LENGTH |
| Round up | CEIL | CEIL | CEILING | CEIL |
| Random value | RAND() | RANDOM() | RAND() | DBMS_RANDOM.VALUE |
| Concatenate | CONCAT() | CONCAT() / || | CONCAT() / + | CONCAT() / || |
| Group to string | GROUP_CONCAT | STRING_AGG | STRING_AGG | LISTAGG |
| Substring | SUBSTRING | SUBSTRING | SUBSTRING | SUBSTR |
For a fuller catalogue of engine-specific behaviour, the advanced SQL functions reference and the core SQL functions pages go deeper than we can here.
Which categories you use most
Not all function families see equal use. In everyday application queries, aggregate and string functions dominate, followed by date handling. The chart below reflects a rough weighting of how often each category shows up in typical reporting and application SQL - useful for deciding what to master first.
If you are early in your SQL journey, learn the aggregate and string families thoroughly first - they cover the majority of real queries. The beginner SQL course takes you through them step by step, and the advanced course covers windowing and heavier date math once the basics are second nature.
Quick answers
What is the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts every row including those with NULLs. COUNT(column) counts only rows where that column is not NULL. Add DISTINCT to count unique non-NULL values.
Why does AVG give an unexpected result?
Because AVG ignores NULLs. If you want NULLs treated as zero, wrap the column in COALESCE(col, 0) before averaging.
Which NULL-fallback function should I use?
Use COALESCE - it is ANSI-standard, works on every engine, and takes any number of arguments. Reserve IFNULL, ISNULL, and NVL for when you are locked to a single engine.
How do I format a date differently per database?
MySQL uses DATE_FORMAT(d, '%Y-%m-%d'); PostgreSQL and Oracle use TO_CHAR(d, 'YYYY-MM-DD'); SQL Server uses FORMAT or CONVERT with a style code. See the cross-database table above.
Where can I practise all of these?
Work through the DQL commands guide for the query surface, keep the SQL cheat sheet open for syntax, and run examples as you read the SQL functions reference.