Functions

String functions

CONCAT

Joins two or more strings together.

SELECT CONCAT(firstname, ' ', lastname) AS fullname FROM users;

Returns e.g. Teppo Testi.

LOWER / UPPER

Converts text to lowercase or uppercase.

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

LENGTH

Returns the number of characters in a string.

SELECT LENGTH('Teppo');  -- 5

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 grades ORDER BY RAND() LIMIT 1;

ABS

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

SELECT ABS(-5);  -- 5

Exercises

1. Use CONCAT to combine first and last name into a full name.
2. Select all names in uppercase using UPPER.
3. Find the length of each name using LENGTH.
4. Use TRIM to clean up a string with extra spaces.
5. Extract the year and month from a date column using YEAR and MONTH.
6. Calculate the number of days between two dates using DATEDIFF.
7. Round a decimal column to 1 decimal place using ROUND.
8. Use FLOOR and CEIL on the same value and compare the results.
9. Return a random row from a table using RAND.
10. Use SUBSTRING to extract the first 3 characters of a name.