SQLite - Useful Functions

Advertisements


SQLite has many built-in functions for performing processing on string or numeric data. Following is the list of few useful SQLite built-in functions and all are case in-sensitive which means you can use these functions either in lower-case form or in upper-case or in mixed form. For more details, you can check official documentation for SQLite:

S.N.Function & Description
1SQLite COUNT Function
The SQLite COUNT aggregate function is used to count the number of rows in a database table.
2SQLite MAX Function
The SQLite MAX aggregate function allows us to select the highest (maximum) value for a certain column.
3SQLite MIN Function
The SQLite MIN aggregate function allows us to select the lowest (minimum) value for a certain column.
4SQLite AVG Function
The SQLite AVG aggregate function selects the average value for certain table column.
5SQLite SUM Function
The SQLite SUM aggregate function allows selecting the total for a numeric column.
6SQLite RANDOM Function
The SQLite RANDOM function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.
7SQLite ABS Function
The SQLite ABS function returns the absolute value of the numeric argument.
8SQLite UPPER Function
The SQLite UPPER function converts a string into upper-case letters.
9SQLite LOWER Function
The SQLite LOWER function converts a string into lower-case letters.
10SQLite LENGTH Function
The SQLite LENGTH function returns the length of a string.
11SQLite sqlite_version Function
The SQLite sqlite_version function returns the version of the SQLite library.

Before we start giving examples on the above-mentioned functions, consider COMPANY table is having 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 count the number of rows in a database table. Following is the example:

sqlite> SELECT count(*) FROM COMPANY;

Above SQLite SQL statement will produce the following result:

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

SQLite MAX Function

The SQLite MAX aggregate function allows us to select the highest (maximum) value for a certain column. Following is the example:

sqlite> SELECT max(salary) FROM COMPANY;

Above SQLite SQL statement will produce the following result:

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

SQLite MIN Function

The SQLite MIN aggregate function allows us to select the lowest (minimum) value for a certain column. Following is the example:

sqlite> SELECT min(salary) FROM COMPANY;

Above SQLite SQL statement will produce the following result:

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

SQLite AVG Function

The SQLite AVG aggregate function selects the average value for certain table column. Following is the example:

sqlite> SELECT avg(salary) FROM COMPANY;

Above SQLite SQL statement will produce the following result:

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

SQLite SUM Function

The SQLite SUM aggregate function allows selecting the total for a numeric column. Following is the example:

sqlite> SELECT sum(salary) FROM COMPANY;

Above SQLite SQL statement will produce the following result:

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

SQLite RANDOM Function

The SQLite RANDOM function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807. Following is the example:

sqlite> SELECT random() AS Random;

Above SQLite SQL statement will produce the following result:

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

SQLite ABS Function

The SQLite ABS function returns the absolute value of the numeric argument. Following is the example:

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

Above SQLite SQL statement will produce the following result:

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 into upper-case letters. Following is the example:

sqlite> SELECT upper(name) FROM COMPANY;

Above SQLite SQL statement will produce the following result:

upper(name)
-----------
PAUL
ALLEN
TEDDY
MARK
DAVID
KIM
JAMES

SQLite LOWER Function

The SQLite LOWER function converts a string into lower-case letters. Following is the example:

sqlite> SELECT lower(name) FROM COMPANY;

Above SQLite SQL statement will produce the following result:

lower(name)
-----------
paul
allen
teddy
mark
david
kim
james

SQLite LENGTH Function

The SQLite LENGTH function returns the length of a string. Following is the example:

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

Above SQLite SQL statement will produce the following result:

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

SQLite sqlite_version Function

The SQLite sqlite_version function returns the version of the SQLite library. Following is the example:

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

Above SQLite SQL statement will produce the following result:

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


Advertisements
Advertisements