SQL Functions

Transform values inside a query with string, number, date and NULL-handling functions.

Lesson 6 of 102Beginner17 min read

The data stored in a table isn't always in the shape you want to show. Names might need capital letters, a long description might need shortening, prices might need rounding and dates might need just the year. Rather than fixing all of that in another program, you can transform values inside the query with functions.

You've already met a few functions: ROUND() in the SELECT lesson and COUNT() and SUM() in the ORDER BY and GROUP BY lesson. Those last two are aggregate functions that summarise many rows. This lesson covers scalar functions, which work on one value at a time and return one value for every row.

Every database system supports the common functions in this lesson, though a few of the names differ. The examples use SQLite, and each section notes the MySQL and PostgreSQL spelling where it's different, so you'll be ready for whichever database you meet first.

Text functions

FunctionExampleResult
UPPER(text)UPPER('leeds')LEEDS
LOWER(text)LOWER('LEEDS')leeds
LENGTH(text)LENGTH('Chloe')5
SUBSTR(text, start, length)SUBSTR('Backpack', 1, 4)Back
TRIM(text)TRIM(' Ana ')Ana
REPLACE(text, from, to)REPLACE('Desk lamp', ' ', '-')Desk-lamp

SQL counts characters from 1, not 0, so SUBSTR('Backpack', 1, 4) starts at the very first letter. To join pieces of text, standard SQL uses ||: name || ' from ' || city. MySQL uses CONCAT(name, ' from ', city) instead, and PostgreSQL accepts both.

Number functions

  • ROUND(x, 2) rounds to two decimal places; ROUND(x) rounds to a whole number.
  • ABS(x) gives the distance from zero, so ABS(-7) is 7.
  • CEIL(x) and FLOOR(x) round up and down. (SQLite added these in version 3.35.)
  • The usual arithmetic operators, + - * /, work on columns too.

Watch out for integer division: in SQLite and PostgreSQL, dividing one whole number by another throws away the remainder, so 7 / 2 is 3. Multiply by 1.0 first, 7 * 1.0 / 2, to get 3.5. MySQL returns a decimal automatically.

Date functions

Dates are where databases differ most. To get the year of the joined column:

DatabaseYearToday's date
SQLitestrftime('%Y', joined)date('now')
MySQLYEAR(joined)CURDATE()
PostgreSQLEXTRACT(YEAR FROM joined)CURRENT_DATE

Storing dates in the standard YYYY-MM-DD format is what makes them sort and compare correctly, even in SQLite where they're stored as text. Date arithmetic also differs: SQLite uses date(joined, '+30 days'), MySQL uses DATE_ADD(joined, INTERVAL 30 DAY) and PostgreSQL uses joined + INTERVAL '30 days'.

Handling NULL with COALESCE and NULLIF

NULL means "no value", and almost any calculation involving NULL gives NULL. Two small functions make NULLs easier to handle:

  • COALESCE(a, b, c) returns the first value that isn't NULL. COALESCE(phone, 'not given') shows a friendly label instead of an empty cell.
  • NULLIF(a, b) returns NULL if a equals b, otherwise a. Its classic use is avoiding division by zero: total / NULLIF(count, 0) gives NULL instead of an error when count is 0.

Both functions are part of standard SQL and work the same way in every major database.

SQL Functions Syntax

Common functions

SELECT UPPER(name), LENGTH(name), SUBSTR(name, 1, 3),
       name || ' (' || city || ')',
       ROUND(price * 1.2, 2),
       COALESCE(phone, 'n/a'),
       total / NULLIF(qty, 0)
FROM table_name;

SQL Functions Summary

  • Scalar functions transform one value per row; aggregate functions summarise many rows.
  • Text: UPPER, LOWER, LENGTH, SUBSTR, TRIM, REPLACE; join text with || or CONCAT.
  • Numbers: ROUND, ABS, CEIL, FLOOR; beware of integer division.
  • Date functions differ between SQLite, MySQL and PostgreSQL.
  • COALESCE replaces NULLs with a fallback; NULLIF turns a value into NULL.

SQL Functions Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Formatting customer names and cities

SQL
SELECT name,
       UPPER(city)  AS city,
       LENGTH(name) AS letters,
       name || ' from ' || city AS label
FROM customers;
Output
name  | city   | letters | label
------+--------+---------+------------------
Ana   | LISBON | 3       | Ana from Lisbon
Ben   | LEEDS  | 3       | Ben from Leeds
Chloe | LYON   | 5       | Chloe from Lyon
Dev   | LEEDS  | 3       | Dev from Leeds
Ema   | PORTO  | 3       | Ema from Porto
Line-by-line explanation
  1. Line 2UPPER() changes only the result, not the stored data.
  2. Line 3LENGTH() counts the characters in each name.
  3. Line 4|| joins text together. In MySQL, write CONCAT(name, ' from ', city).
Example 2

Building product codes

SQL
SELECT UPPER(SUBSTR(category, 1, 3)) || '-' || id AS code,
       name,
       ROUND(price * 0.9, 2) AS sale_price
FROM products
ORDER BY code;
Output
code  | name         | sale_price
------+--------------+-----------
BAG-3 | Backpack     | 31.49
HOM-6 | Desk lamp    | 22.05
KIT-4 | Water bottle | 10.8
KIT-5 | Lunch box    | 7.88
STA-1 | Notebook     | 3.15
STA-2 | Pen          | 1.08
Line-by-line explanation
  1. Line 1The first three letters of the category in capitals, a dash, then the id.
  2. Line 3A 10 percent discount, rounded to two decimals.
  3. Line 5You can sort by an alias defined in the SELECT list.
Example 3

Working with dates

SQL
SELECT name,
       joined,
       strftime('%Y', joined)          AS year_joined,
       date(joined, '+1 year')         AS first_anniversary
FROM customers
WHERE joined >= '2024-01-01';
Output
name  | joined     | year_joined | first_anniversary
------+------------+-------------+------------------
Chloe | 2024-01-03 | 2024        | 2025-01-03
Dev   | 2024-03-15 | 2024        | 2025-03-15
Ema   | 2024-06-30 | 2024        | 2025-06-30
Line-by-line explanation
  1. Line 3strftime('%Y', ...) pulls out the year in SQLite. MySQL: YEAR(joined).
  2. Line 4date(..., '+1 year') adds a year. MySQL: DATE_ADD(joined, INTERVAL 1 YEAR).
  3. Line 6ISO dates compare correctly as text, so this finds everyone who joined in 2024.

Common SQL Functions Mistakes

Counting from 0 in SUBSTR

Don't
SELECT SUBSTR('Backpack', 0, 4);   -- 'Bac' in SQLite
Do
SELECT SUBSTR('Backpack', 1, 4);   -- 'Back'

SQL positions start at 1. Starting at 0 gives a confusing, shorter result.

Joining text with + in standard SQL

Don't
SELECT name + ' from ' + city FROM customers;
Do
SELECT name || ' from ' || city FROM customers;

+ is for numbers. It only joins text in SQL Server; MySQL uses CONCAT().

Dividing whole numbers

Don't
SELECT 7 / 2;          -- 3 in SQLite and PostgreSQL
Do
SELECT 7 * 1.0 / 2;    -- 3.5

SQL Functions in Real Projects

Functions in reporting

Reports almost always reshape data on the way out: names in proper case, prices rounded to two decimals, dates grouped by month with strftime('%Y-%m', order_date) and missing values shown as "n/a" with COALESCE. Doing this inside the query keeps the application code simple and makes the same report easy to reuse in a spreadsheet or a dashboard.

SQL Functions FAQ

How do I join text in MySQL, PostgreSQL and SQLite?

PostgreSQL and SQLite use the standard || operator: name || ' (' || city || ')'. MySQL treats || as logical OR by default, so use CONCAT(name, ' (', city, ')') there; PostgreSQL also supports CONCAT. A difference to remember is that || with a NULL gives NULL, while CONCAT in PostgreSQL skips NULL arguments.

Why does ROUND give different results in different databases?

For exact decimal types, most databases round halves away from zero, so ROUND(2.5) is 3. For floating-point values, some use "round half to even": in PostgreSQL, round(2.5::numeric) is 3 but round(2.5::float8) is 2. Store money as DECIMAL to get predictable rounding.

Does LENGTH count characters or bytes?

In SQLite and PostgreSQL, LENGTH counts characters, so LENGTH('café') is 4. In MySQL, LENGTH counts bytes and returns 5, because é uses two bytes in UTF-8; use CHAR_LENGTH there for characters. PostgreSQL's OCTET_LENGTH gives the byte count.

Do functions in WHERE make queries slower?

They can. WHERE UPPER(city) = 'LEEDS' or WHERE strftime('%Y', joined) = '2024' must calculate the function for every row, so an ordinary index on the column can't be used. Compare the raw column with a range where possible, or create an index on the expression, as the indexes lesson explains.

Can I write my own SQL functions?

Yes, in most server databases. PostgreSQL and MySQL support CREATE FUNCTION, and SQL Server and Oracle have their own versions; SQLite lets the host program register functions instead. The triggers and procedures lesson introduces code that lives inside the database.

SQL Functions Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the query so every city is shown in capital letters.

SELECT (city) FROM customers;
Predict the outputeasy

What does this return?

SELECT LENGTH('Water bottle');
Predict the outputmedium

What does this return?

SELECT COALESCE(NULL, NULL, 'none', 'other');
Multiple choicemedium

Which expression avoids a division-by-zero error when qty is 0?

Predict the outputmedium

What does this return?

SELECT SUBSTR('Notebook', 5, 4);

SQL Functions Quiz

5 questions to check what you learned.

  1. What kind of function returns one value for each row?

    • An aggregate function
    • A window function
    • A scalar function
    • A table function

  2. What does ROUND(8.756, 1) return?

    • 8.7
    • 8.8
    • 9
    • 8.75

  3. How do you join two pieces of text in standard SQL?

    • With +
    • With &
    • With .
    • With ||

  4. What does COALESCE(phone, 'n/a') show when phone is NULL?

    • n/a
    • NULL
    • An empty string
    • An error

  5. Why can WHERE LOWER(email) = 'a@b.com' be slow on a big table?

    • LOWER is deprecated
    • Text comparisons are always slow
    • The function runs on every row, so a normal index on email can't be used
    • It returns duplicate rows

Related SQL topics

02SELECTRead exactly the columns you need, rename them, calculate new values and remove duplicates.07CASE ExpressionsAdd if-then-else logic to a query with CASE, to label rows, create categories and count conditionally.05ORDER BY & GROUP BYSort results, return only the top rows, and summarise data with COUNT, SUM, AVG and GROUP BY.
Esc
↑↓ to navigateEnter to open