One of the things I appreciate most about SQLite is how much you can accomplish using nothing but plain SQL, thanks to its rich set of built-in functions. Whether you need to manipulate strings, crunch numbers, aggregate data, or work with dates, SQLite has a function ready for the job. In this article, I am going to walk through the major categories of built-in functions SQLite provides, show practical examples for each, and share some tips on when and how to use them effectively.
Why Built-In Functions Matter
Before diving in, it is worth explaining why these functions are so useful in the first place. Without them, you would have to pull raw data out of the database and process it in your application code, which means more round trips, more data transferred, and more code to maintain. By pushing logic like string formatting, aggregation, or date calculations into the SQL layer itself, your queries become more powerful and your application code becomes simpler. I use built-in functions constantly, whether I am cleaning up messy text data, generating reports, or filtering records based on calculated values.
Core Function Categories
SQLite’s built-in functions generally fall into a few categories: scalar functions (which operate on a single value and return a single value), aggregate functions (which operate on a group of rows and return one summarizing value), and a handful of specialized functions for JSON, math, and date/time work. Let me go through each category.
String Functions
String manipulation is something almost every application needs, and SQLite covers the essentials well.
LENGTH() returns the number of characters in a string:
SELECT LENGTH('SQLite'); -- returns 6
UPPER() and LOWER() change the case of text:
SELECT UPPER('hello world'); -- 'HELLO WORLD'
SELECT LOWER('HELLO WORLD'); -- 'hello world'
SUBSTR() extracts a portion of a string. It takes the string, a starting position (1-indexed), and an optional length:
SELECT SUBSTR('Hello, World!', 8, 5); -- 'World'
TRIM(), LTRIM(), RTRIM() remove whitespace (or other specified characters) from a string:
SELECT TRIM(' padded text '); -- 'padded text'
SELECT LTRIM('xxHello', 'x'); -- 'Hello'
REPLACE() substitutes occurrences of one substring with another:
SELECT REPLACE('2024-01-15', '-', '/'); -- '2024/01/15'
INSTR() finds the position of a substring inside another string, returning 0 if it is not found:
SELECT INSTR('Hello, World!', 'World'); -- 8
|| (concatenation operator) joins strings together. It is technically an operator rather than a function, but it belongs in this list since it is used so often:
SELECT first_name || ' ' || last_name AS full_name FROM users;
I use this concatenation pattern all the time when building display names or composing formatted output directly in a query.
PRINTF() / FORMAT() gives you C-style string formatting, which is incredibly handy for building formatted output:
SELECT PRINTF('%05d - %s', 42, 'Widget'); -- '00042 - Widget'
Numeric and Math Functions
SQLite provides a solid set of mathematical functions, especially in newer versions.
ABS() returns the absolute value:
SELECT ABS(-15); -- 15
ROUND() rounds a number to a specified number of decimal places:
SELECT ROUND(3.14159, 2); -- 3.14
MAX() and MIN() can be used both as scalar functions comparing several arguments and as aggregate functions across rows:
SELECT MAX(5, 12, 3); -- 12 (scalar usage)
SELECT MAX(price) FROM products; -- aggregate usage
RANDOM() generates a pseudo-random integer, useful for things like selecting a random row:
SELECT * FROM products ORDER BY RANDOM() LIMIT 1;
SQRT(), POWER(), and other math functions are available in SQLite versions built with the math extension enabled (which is the default in most modern distributions):
SELECT SQRT(144); -- 12.0
SELECT POWER(2, 10); -- 1024.0
I find these especially useful when doing quick calculations directly in a report query, rather than pulling raw numbers into the application just to do simple math.
Aggregate Functions
Aggregate functions summarize data across multiple rows, and they are essential for reporting and analytics work.
COUNT() counts rows:
SELECT COUNT(*) FROM orders;
SELECT COUNT(DISTINCT customer_id) FROM orders;
SUM() adds up numeric values:
SELECT SUM(total_amount) FROM orders WHERE status = 'completed';
AVG() calculates the average:
SELECT AVG(price) FROM products;
MIN() and MAX() find the smallest and largest values in a group:
SELECT MIN(price), MAX(price) FROM products;
GROUP_CONCAT() is one of my favorites; it concatenates values from multiple rows into a single string, optionally with a custom separator:
SELECT department, GROUP_CONCAT(name, ', ') AS employees
FROM staff
GROUP BY department;
This is incredibly handy for generating summary reports without needing to loop through results in application code.
Aggregate functions are almost always paired with GROUP BY when you want per-category summaries, and with HAVING when you need to filter based on the aggregated result:
SELECT department, COUNT(*) AS employee_count
FROM staff
GROUP BY department
HAVING COUNT(*) > 5;
Date and Time Functions
SQLite has a small but powerful set of functions for handling dates and times, built around DATE(), TIME(), DATETIME(), JULIANDAY(), and STRFTIME(). I cover this topic in much greater depth in a dedicated article, but here is a quick taste:
SELECT DATE('now'); -- current date
SELECT DATETIME('now', 'localtime'); -- current local datetime
SELECT STRFTIME('%Y-%m', order_date) AS month FROM orders;
These functions are essential for any application that needs to filter, group, or format data by date, which is to say, almost every application.
Conditional Functions
COALESCE() returns the first non-null value from a list of arguments, which is extremely useful for providing default values:
SELECT COALESCE(nickname, first_name, 'Unknown') FROM users;
IFNULL() is a two-argument shorthand version of the same idea:
SELECT IFNULL(phone_number, 'Not provided') FROM contacts;
NULLIF() returns NULL if two expressions are equal, which is handy for avoiding division-by-zero errors:
SELECT total / NULLIF(count, 0) FROM statistics;
CASE expressions, while technically part of core SQL rather than a function, work hand in hand with these to build conditional logic directly in your queries:
SELECT name,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'F'
END AS grade
FROM students;
I use this pattern constantly when building reports that need to bucket or categorize numeric data without post-processing in application code.
Type Conversion Functions
CAST() explicitly converts a value from one type to another:
SELECT CAST('42' AS INTEGER);
SELECT CAST(price AS TEXT) || ' USD' FROM products;
TYPEOF() tells you the storage class SQLite is using for a given value, which is genuinely useful for debugging when you are dealing with SQLite’s flexible typing system:
SELECT TYPEOF(price) FROM products LIMIT 1;
JSON Functions
Modern versions of SQLite include a comprehensive set of JSON functions, letting you store and query semi-structured data directly.
SELECT JSON_EXTRACT('{"name": "Ali", "age": 30}', '$.name'); -- 'Ali'
SELECT JSON_OBJECT('id', 1, 'name', 'Widget') AS json_data;
SELECT json_each.value
FROM json_each('["red", "green", "blue"]');
I have used these JSON functions in projects where I needed the flexibility of a document-style field alongside otherwise structured relational data, and it works surprisingly well for that hybrid use case.
Window Functions
While technically a broader SQL feature rather than a single “function,” SQLite supports window functions like ROW_NUMBER(), RANK(), LAG(), and LEAD(), which let you perform calculations across a set of rows related to the current row, without collapsing them into a single aggregated result.
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;
This is enormously useful for tasks like finding the top N records per group, calculating running totals, or comparing a row to the previous one.
SELECT order_date, total_amount,
SUM(total_amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
Practical Example: Combining Functions in a Report Query
Here is a realistic example that combines several of these functions to build a monthly sales report:
SELECT
STRFTIME('%Y-%m', order_date) AS month,
COUNT(*) AS total_orders,
ROUND(SUM(total_amount), 2) AS revenue,
ROUND(AVG(total_amount), 2) AS average_order_value,
GROUP_CONCAT(DISTINCT customer_region) AS regions
FROM orders
WHERE status = 'completed'
GROUP BY month
ORDER BY month DESC;
This single query pulls together date formatting, aggregation, rounding, and string concatenation to produce a polished, ready-to-use report, without any post-processing needed in the application layer.
Best Practices When Using Built-In Functions
Push work into the database when it makes sense. If you find yourself looping through query results in application code just to format, sum, or filter values, there is a good chance a built-in function can do it faster and with less code.
Be mindful of NULL handling. Many aggregate functions ignore NULL values silently, which is usually what you want, but it can occasionally produce misleading results if you are not paying attention, particularly with AVG() and COUNT().
Watch out for type affinity quirks. Since SQLite uses dynamic typing, functions like MAX() and comparison operators can behave in ways that surprise developers coming from strictly typed databases. Use TYPEOF() when debugging unexpected results.
Index columns used in WHERE, GROUP BY, and ORDER BY clauses, especially when working with date functions, since wrapping a column in a function can sometimes prevent SQLite from using an index effectively unless you use expression indexes.
Test your STRFTIME() format strings carefully, since date and time formatting mistakes are one of the most common sources of subtle bugs in reporting queries.
Wrapping Up
SQLite’s built-in function library is more extensive than most people expect from what is often thought of as a “lightweight” database engine. String manipulation, math, aggregation, conditional logic, type conversion, JSON handling, and window functions together cover the vast majority of what you need for building real reports and application logic directly inside your queries. Once you get comfortable weaving these functions together, as shown in the report example above, you will find yourself writing far less application-side processing code, and your queries will do more of the heavy lifting for you. If you want to go deeper on any single category, date and time handling deserves its own focused study, since it is one of the areas where small mistakes cause the most confusion, and I cover that in detail in a separate article.
