SQLite Common Functions

SQLite has many built-in functions that can process string or numeric data. Below is a list of some useful SQLite built-in functions, all of which are case-insensitive, meaning you can use these functions in lowercase, uppercase, or mixed case. For more detailed information, you can refer to the official SQLite documentation.

Before providing examples of the above functions, consider the COMPANY table with the following records.

ID                         NAME                         AGE                                 ADDRESS                 SALARY
----------　　----------　　----------　　----------　　----------
1　　　　　　　　　　　Paul　　　　　　　　32　　　　　　　　　　California　　20000.0
2　　　　　　　　　　　Allen　　　　　　　25　　　　　　　　　　Texas　　　　　　　15000.0
3　　　　　　　　　　　Teddy　　　　　　　23　　　　　　　　　　Norway　　　　　　20000.0
4　　　　　　　　　　　Mark　　　　　　　　25　　　　　　　　　　Rich-Mond　　　65000.0
5　　　　　　　　　　　David　　　　　　　27　　　　　　　　　　Texas　　　　　　　85000.0
6　　　　　　　　　　　Kim　　　　　　　　　22　　　　　　　　　　South-Hall　　45000.0
7　　　　　　　　　　　James　　　　　　　24　　　　　　　　　　Houston　　　　　10000.0

SQLite COUNT function

The SQLite COUNT aggregate function is used to calculate the number of rows in a database table. Here is an example-

sqlite> SELECT count(*) FROM COMPANY;

The following SQLite SQL statements will produce the following content.

count(*)
----------
7

SQLite MAX function

The SQLite MAX aggregate function enables us to select the highest (maximum) value for a specific column. Here is an example-

sqlite> SELECT max(salary) FROM COMPANY;

The following SQLite SQL statements will produce the following content.

max(salary)
-----------
85000.0

SQLite MIN function

The SQLite MIN aggregate function allows us to select the lowest (minimum) value for a specific column. Here is an example-

sqlite>　SELECT　min(salary)　FROM　COMPANY;

The following SQLite SQL statements will produce the following content.

min(salary)
-----------
10000.0

SQLite AVG function

The SQLite AVG aggregate function selects the average of a column in a table. Here is an example-

sqlite>　SELECT　avg(salary)　FROM　COMPANY;

The following SQLite SQL statements will produce the following content.

avg(salary)
----------------
37142.8571428572

SQLite SUM function

The SQLite SUM aggregate function allows you to select the total for a numeric column. Here is an example-

sqlite>　SELECT　sum(salary)　FROM　COMPANY;

The following SQLite SQL statements will produce the following content.

sum(salary)
-----------
260000.0

SQLite RANDOM function

The SQLite RANDOM function returns-9223372036854775808and+9223372036854775807a pseudo-random integer between. Here is an example-

sqlite>　SELECT　random()　AS　Random;

The following SQLite SQL statements will produce the following content.

Random
-------------------
5876796417670984050

SQLite ABS function

The SQLite ABS function returns the absolute value of a numeric parameter. Here is an example-

sqlite>　SELECT　abs(5),　abs(-15),　abs(NULL),　abs(0),　abs("ABC");

The following SQLite SQL statements will produce the following content.

abs(5)　　　　　　abs(-15)　　　　abs(NULL)　　　abs(0)　　　　　　abs("ABC")
----------　　----------　　----------　　----------　　----------
5　　　　　　　　　　　15　　　　　　　　　　　　　　　　　　　　　　0　　　　　　　　　　　0.0

SQLite UPPER function

The SQLite UPPER function converts a string to uppercase letters. Here is an example-

sqlite>　SELECT　upper(name)　FROM　COMPANY;

The following SQLite SQL statements will produce the following content.

upper(name)
-----------
PAULALLENTEDDYMARKDAVIDKIMJAMES

SQLite LOWER function

The SQLite LOWER function converts a string to lowercase letters. Here is an example-

sqlite>　SELECT　lower(name)　FROM　COMPANY;

The following SQLite SQL statements will produce the following content.

lower(name)
-----------
paulallenteddymarkdavidkimjames

SQLite LENGTH function

The SQLite LENGTH function returns the length of a string. Here is an example-

sqlite>　SELECT　name,　length(name)　FROM　COMPANY;

The following SQLite SQL statements will produce the following content.

NAME　　　　　　　　length(name)
----------　　------------
Paul　　　　　　　　4
Allen　　　　　　　5
Teddy　　　　　　　5
Mark　　　　　　　　4
David　　　　　　　5
Kim　　　　　　　　　3
James　　　　　　　5

SQLite sqlite_version function

The sqlite_version function returns the version of the SQLite library. Here is an example-

sqlite> SELECT sqlite_version() AS 'SQLite Version';

The following SQLite SQL statements will produce the following content.

SQLite Version--------------3.6.20