SQLite - Useful Functions

SQLite - Useful Functions

SQLite provides a rich set of built-in functions for performing calculations and data manipulation. These functions are case-insensitive. Here we cover some of the most commonly used functions, grouped by category.

For our examples, we will use the following EMPLOYEES table:

CREATE TABLE EMPLOYEES(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   DEPARTMENT     TEXT    NOT NULL,
   SALARY         REAL,
   JOIN_DATE      TEXT
);

INSERT INTO EMPLOYEES VALUES (1, 'Alice', 32, 'Engineering', 95000.0, '2020-03-15');
INSERT INTO EMPLOYEES VALUES (2, 'Bob', 25, 'Marketing', 68000.0, '2021-07-22');
INSERT INTO EMPLOYEES VALUES (3, 'Charlie', 28, 'Engineering', 105000.0, '2019-01-10');
INSERT INTO EMPLOYEES VALUES (4, 'David', 45, 'Sales', 120000.0, '2017-05-30');
INSERT INTO EMPLOYEES VALUES (5, 'Eve', 25, 'Engineering', 88000.0, '2022-11-01');

Aggregate Functions

Aggregate functions perform a calculation on a set of rows and return a single summary value.

Function	Description & Example
COUNT(expr)	Counts the number of rows. COUNT(*) counts all rows; COUNT(column) counts non-NULL values in that column. SELECT COUNT(*) FROM EMPLOYEES; → 5
MAX(expr)	Returns the maximum value in a group. SELECT MAX(SALARY) FROM EMPLOYEES; → 120000.0
MIN(expr)	Returns the minimum value in a group. SELECT MIN(AGE) FROM EMPLOYEES; → 25
AVG(expr)	Returns the average value of a group. SELECT AVG(AGE) FROM EMPLOYEES; → 31.0
SUM(expr)	Returns the sum of all values in a group. SELECT SUM(SALARY) FROM EMPLOYEES; → 476000.0
GROUP_CONCAT(expr)	Concatenates non-NULL values from a group into a single string, separated by commas. SELECT DEPARTMENT, GROUP_CONCAT(NAME) FROM EMPLOYEES GROUP BY DEPARTMENT;

Scalar Core & String Functions

Scalar functions operate on a single value and return a single value.

Function	Description & Example
LENGTH(str)	Returns the number of characters in a string. SELECT NAME, LENGTH(NAME) FROM EMPLOYEES WHERE ID = 1; → 'Alice', 5
UPPER(str)	Converts a string to uppercase. SELECT UPPER(NAME) FROM EMPLOYEES WHERE ID = 1; → 'ALICE'
LOWER(str)	Converts a string to lowercase. SELECT LOWER(NAME) FROM EMPLOYEES WHERE ID = 1; → 'alice'
ABS(num)	Returns the absolute (non-negative) value of a number. SELECT ABS(-15.5); → 15.5
RANDOM()	Returns a pseudo-random 64-bit signed integer. SELECT RANDOM(); → (a large random number)
TYPEOF(expr)	Returns the data type of an expression: ‘null’, ‘integer’, ‘real’, ‘text’, or ‘blob’. SELECT TYPEOF(SALARY) FROM EMPLOYEES LIMIT 1; → 'real'
SUBSTR(str, start, len)	Extracts a substring of len characters starting at position start (1-based). SELECT SUBSTR('SQLite', 1, 3); → 'SQL'

Date & Time Functions

These functions are essential for working with date and time information.

Function	Description & Example
DATE(timestring, ...)	Returns the date in YYYY-MM-DD format. SELECT DATE(JOIN_DATE, '+1 year') FROM EMPLOYEES WHERE ID = 1; → '2021-03-15'
STRFTIME(format, time, ...)	Formats a date/time according to the format string. Very powerful. SELECT STRFTIME('%Y', JOIN_DATE) FROM EMPLOYEES WHERE ID = 1; → '2020'
JULIANDAY(timestring)	Returns the Julian day number - useful for calculating differences between dates. SELECT JULIANDAY('now') - JULIANDAY(JOIN_DATE) FROM EMPLOYEES WHERE ID = 1; → (days since joining)

Window Functions (Modern SQLite)

Window functions perform calculations across a set of table rows that are somehow related to the current row. Unlike aggregate functions, they do not collapse rows; they return a value for each row.

Example: Rank employees by salary within each department.

SELECT
  NAME,
  DEPARTMENT,
  SALARY,
  RANK() OVER (PARTITION BY DEPARTMENT ORDER BY SALARY DESC) as rank_in_dept
FROM EMPLOYEES;

This would produce a result showing each employee’s salary rank within their specific department.

System Functions

These functions provide metadata about the SQLite library itself.

sqlite> SELECT sqlite_version();
-- Returns the version number of the SQLite library.
-- Example Result: '3.41.2'