Functions

String functions

CONCAT

Joins two or more strings together.

SELECT CONCAT(age), ' ', name) AS combined FROM people;

Returns e.g. 12 Adam.

LOWER / UPPER

Converts text to lowercase or uppercase.

SELECT LOWER('Hello World');  -- hello world
SELECT UPPER('Hello World');  -- HELLO WORLD

For example

SELECT name, upper(city) FROM people;

LENGTH

Returns the number of characters in a string.

SELECT LENGTH('Teppo');  -- 5

Or from sample database

SELECT LENGTH(name) as name_length, name FROM people;

TRIM

Removes leading and trailing whitespace from a string.

SELECT TRIM('  hello  ');  -- hello

You can also trim only one side:

SELECT LTRIM('  hello');  -- hello
SELECT RTRIM('hello  ');  -- hello

SUBSTRING

Extracts a part of a string. Takes the string, start position, and length.

SELECT SUBSTRING('Hello World', 1, 5);  -- Hello

REPLACE

Replaces occurrences of a substring within a string.

SELECT REPLACE('Hello World', 'World', 'SQL');  -- Hello SQL

Date functions

NOW

Returns the current date and time.

SELECT NOW();  -- 2024-06-15 14:30:00

DATE / TIME

Extracts the date or time part from a datetime value.

SELECT DATE(NOW());  -- 2024-06-15
SELECT TIME(NOW());  -- 14:30:00

YEAR / MONTH / DAY

Extracts a specific part from a date.

SELECT YEAR('2024-06-15');   -- 2024
SELECT MONTH('2024-06-15');  -- 6
SELECT DAY('2024-06-15');    -- 15

DATEDIFF

Returns the number of days between two dates.

SELECT DATEDIFF('2024-12-31', '2024-01-01');  -- 365

The first argument is the later date, the second is the earlier date.

DATE_ADD / DATE_SUB

Adds or subtracts a time interval from a date.

SELECT DATE_ADD('2024-01-01', INTERVAL 30 DAY);   -- 2024-01-31
SELECT DATE_SUB('2024-06-15', INTERVAL 1 MONTH);  -- 2024-05-15

Numeric functions

ROUND

Rounds a number to a specified number of decimal places.

SELECT ROUND(3.14159, 2);  -- 3.14
SELECT ROUND(3.5);          -- 4

FLOOR / CEIL

FLOOR rounds down, CEIL rounds up to the nearest integer.

SELECT FLOOR(3.7);  -- 3
SELECT CEIL(3.2);   -- 4

MOD

Returns the remainder of a division.

SELECT MOD(10, 3);  -- 1

RAND

Returns a random decimal number between 0 and 1.

SELECT RAND();  -- e.g. 0.7324

Useful for returning rows in random order:

SELECT * FROM people ORDER BY RAND() LIMIT 1;

ABS

Returns the absolute (non-negative) value of a number.

SELECT ABS(-5);  -- 5