SQLite Common functions

SqliteCommon Functions

Reference:

SQLite Common Functions | Beginner's Tutorial
Http://www.runoob.com/sqlite/sqlite-functions.html

SqliteCommon Functions

SQLite has many built-in functions for working with string or numeric data. Some useful SQLite built-in functions are listed below, and all functions are case insensitive, which means you can use lowercase or uppercase or mixed forms of these functions. For more information, please check out the official SQLite documentation:

Serial Number	Functions & Descriptions
1	SQLite COUNT function The SQLite count aggregate function is used to calculate the number of rows in a database table.
2	SQLite MAX function The SQLite max aggregation function allows us to select the maximum value for a column.
3	SQLite MIN function The SQLite min aggregation function allows us to select the minimum value for a column.
4	SQLite AVG function The SQLite AVG aggregation function calculates the average of a column.
5	SQLite SUM function The SQLite sum aggregation function allows you to calculate a sum for a numeric column.
6	SQLite RANDOM function The SQLite random function returns a pseudo-random integer between 9223372036854775808 and +9223372036854775807.
7	SQLite ABS function The SQLite ABS function returns the absolute value of the numeric parameter.
8	SQLite UPPER function The SQLite UPPER function converts a string to uppercase.
9	SQLite LOWER function The SQLite LOWER function converts a string to lowercase letters.
10	SQLite LENGTH function The SQLite length function returns the lengths of the strings.
11	SQLite sqlite_version function The SQLite sqlite_version function returns the version of the SQLite library.

Before we begin to explain these function instances, let's assume that the company table has 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 above SQLite SQL statement will produce the following results:

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

SQLite MAX function

The SQLite max aggregation function allows us to select the maximum value for a column. Here is an example:

SQLite> SELECT max(salary)        from company;

The above SQLite SQL statement will produce the following results:

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

SQLite MIN function

The SQLite min aggregation function allows us to select the minimum value for a column. Here is an example:

SQLite> SELECT min(salary)        from company;

The above SQLite SQL statement will produce the following results:

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

SQLite AVG function

The SQLite AVG aggregation function calculates the average of a column. Here is an example:

SQLite> SELECT avg(salary)        from company;

The above SQLite SQL statement will produce the following results:

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

SQLite SUM function

The SQLite sum aggregation function allows you to calculate a sum for a numeric column. Here is an example:

SQLite> SELECT sum(salary)        from company;

The above SQLite SQL statement will produce the following results:

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

SQLite RANDOM function

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

SQLite> SELECT random()random;    

The above SQLite SQL statement will produce the following results:

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

SQLite ABS function

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

SQLite> SELECT abs(5), ABS ( --  ), ABS (NULL), ABS (0), abs("ABC");           

The above SQLite SQL statement will produce the following results:

 
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. Here is an example:

SQLite> SELECT Upper(name)        from company;

The above SQLite SQL statement will produce the following results:

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

SQLite LOWER function

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

SQLite> SELECT lower(name)        from company;

The above SQLite SQL statement will produce the following results:

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

SQLite LENGTH function

The SQLite length function returns the lengths of the strings. Here is an example:

SQLite> SELECT name, length(name)        from company; 

The above SQLite SQL statement will produce the following results:

 
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. Here is an example:

SQLite> SELECT sqlite_version()' SQLite version ';    

The above SQLite SQL statement will produce the following results:

SQLiteVersion--------------3.6.      -

SQLite Common functions