Lesson 10 of 26 · see the practice database

Functions for text and numbers

A function takes a value, does something to it and gives back a new value. UPPER('asha') gives 'ASHA'. SQL has functions for text, for numbers and for dates. This lesson covers the text and number functions that you will use every week. The functions here work on one row at a time. The next lesson covers a different family that works on many rows at once.

Changing case

SELECT name, UPPER(name) AS capitals, LOWER(name) AS small
FROM customers
LIMIT 4;

The table is not changed. Functions in a SELECT change only what is shown.

Comparing without caring about case

In lesson 3, searching for 'delhi' found nothing because the table says 'Delhi'. Turn the column to small letters before comparing, and the search works however the text was typed.

SELECT name, city
FROM customers
WHERE LOWER(city) = 'delhi';

This is the standard way to make a comparison ignore case in any database.

Length

SELECT name, LENGTH(name) AS letters
FROM products
ORDER BY letters DESC
LIMIT 3;

LENGTH counts characters, including spaces. In SQL Server the function is called LEN.

Removing spaces at the ends

Data typed by people often has stray spaces, and 'Delhi ' with a space does not equal 'Delhi'. TRIM removes spaces from both ends. LTRIM and RTRIM do one side.

SELECT '[' || TRIM('   Delhi  ') || ']' AS trimmed,
       LENGTH('   Delhi  ') AS before,
       LENGTH(TRIM('   Delhi  ')) AS after;

Part of a text

SUBSTR(text, start, length) cuts a piece out. Positions start at 1, not 0.

SELECT order_date,
       SUBSTR(order_date, 1, 4) AS year,
       SUBSTR(order_date, 6, 2) AS month
FROM orders
LIMIT 3;

Leave out the length to take everything from the start position to the end. MySQL and PostgreSQL accept SUBSTR too, and also SUBSTRING. SQL Server has only SUBSTRING.

Replace

REPLACE(text, find, put) swaps every occurrence of one piece of text for another.

SELECT name, REPLACE(name, ' kg', ' kilogram') AS long_name
FROM products
WHERE name LIKE '% kg';

Finding where something is

INSTR(text, piece) gives the position of the first match, or 0 if there is none. Combined with SUBSTR, it splits text at a marker. This takes the first name from a full name by cutting at the space.

SELECT name,
       SUBSTR(name, 1, INSTR(name, ' ') - 1) AS first_name
FROM employees
LIMIT 4;

INSTR exists in SQLite, MySQL and Oracle. PostgreSQL uses POSITION(piece IN text) and SQL Server uses CHARINDEX(piece, text).

Rounding

ROUND(number, places) rounds to the number of decimal places you give. With no second value it rounds to a whole number.

SELECT name, price,
       price * 1.18 AS exact,
       ROUND(price * 1.18, 2) AS two_places,
       ROUND(price * 1.18) AS whole
FROM products
LIMIT 4;

Always round money for display. Unrounded results of multiplication and division often carry a long tail of digits.

Other number functions

SELECT ABS(-250) AS size,
       10 % 3 AS remainder,
       MAX(3, 9, 5) AS largest,
       MIN(3, 9, 5) AS smallest;

ABS removes the minus sign. % gives the remainder after division; some databases write it MOD(10, 3). Using MAX and MIN with several values in one row, as here, is a SQLite feature. MySQL and PostgreSQL call those GREATEST and LEAST.

Changing the type with CAST

Sometimes a number is stored as text, or you want a whole number treated as a decimal. CAST(value AS type) converts.

SELECT CAST('42' AS INTEGER) + 1 AS number,
       CAST(7 AS REAL) / 2 AS half,
       CAST(SUBSTR('2026-03-15', 1, 4) AS INTEGER) AS year;

The middle column is the tidy cure for the whole-number division trap from lesson 2.

Functions inside functions

The result of one function can be handed to another. Read from the inside out.

SELECT name,
       UPPER(SUBSTR(name, 1, 3)) AS code
FROM products
LIMIT 4;

SUBSTR takes the first three letters and UPPER turns them into capitals.

Functions in WHERE and ORDER BY

A function can be used anywhere a value can.

SELECT name
FROM customers
WHERE LENGTH(name) > 10
ORDER BY LENGTH(name) DESC;

One caution for large tables: wrapping a column in a function inside WHERE usually stops the database from using an index on that column, so the search reads every row. On small tables it does not matter.

Names differ between databases

Function names are the least standard part of SQL. The ideas are the same everywhere, and the spelling is not.

When a function from one product fails in another, search that product's manual for the job you want. The equivalent nearly always exists.

Try it yourself

SELECT UPPER(name) FROM customers;

SELECT name, ROUND(price * 0.9) AS sale_price FROM products;

SELECT name, SUBSTR(joined, 1, 4) AS year FROM customers;

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.