sqlite has many built-in functions to perform 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.
| sr.no. | function & description |
|---|---|
| 1 |
sqlite count function sqlite count aggregate function is used to count the number of rows in a database table. |
| 2 |
sqlite max function sqlite max aggregate function allows us to select the highest (maximum) value for a certain column. |
| 3 |
sqlite min function sqlite min aggregate function allows us to select the lowest (minimum) value for a certain column. |
| 4 |
sqlite avg function sqlite avg aggregate function selects the average value for certain table column. |
| 5 |
sqlite sum function sqlite sum aggregate function allows selecting the total for a numeric column. |
| 6 |
sqlite random function sqlite random function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807. |
| 7 |
sqlite abs function sqlite abs function returns the absolute value of the numeric argument. |
| 8 |
sqlite upper function sqlite upper function converts a string into upper-case letters. |
| 9 |
sqlite lower function sqlite lower function converts a string into lower-case letters. |
| 10 |
sqlite length function sqlite length function returns the length of a string. |
| 11 |
sqlite sqlite_version function sqlite sqlite_version function returns the version of the sqlite library. |
before we start giving examples on the above-mentioned functions, consider 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
sqlite count aggregate function is used to count the number of rows in a database table. following is an example −
sqlite> select count(*) from company;
the above sqlite sql statement will produce the following.
count(*) ---------- 7
sqlite max function
sqlite max aggregate function allows us to select the highest (maximum) value for a certain column. following is an example −
sqlite> select max(salary) from company;
the above sqlite sql statement will produce the following.
max(salary) ----------- 85000.0
sqlite min function
sqlite min aggregate function allows us to select the lowest (minimum) value for a certain column. following is an example −
sqlite> select min(salary) from company;
the above sqlite sql statement will produce the following.
min(salary) ----------- 10000.0
sqlite avg function
sqlite avg aggregate function selects the average value for a certain table column. following is an the example −
sqlite> select avg(salary) from company;
the above sqlite sql statement will produce the following.
avg(salary) ---------------- 37142.8571428572
sqlite sum function
sqlite sum aggregate function allows selecting the total for a numeric column. following is an example −
sqlite> select sum(salary) from company;
the above sqlite sql statement will produce the following.
sum(salary) ----------- 260000.0
sqlite random function
sqlite random function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807. following is an example −
sqlite> select random() as random;
the above sqlite sql statement will produce the following.
random ------------------- 5876796417670984050
sqlite abs function
sqlite abs function returns the absolute value of the numeric argument. following is an example −
sqlite> select abs(5), abs(-15), abs(null), abs(0), abs("abc");
the above sqlite sql statement will produce the following.
abs(5) abs(-15) abs(null) abs(0) abs("abc")
---------- ---------- ---------- ---------- ----------
5 15 0 0.0
sqlite upper function
sqlite upper function converts a string into upper-case letters. following is an example −
sqlite> select upper(name) from company;
the above sqlite sql statement will produce the following.
upper(name) ----------- paul allen teddy mark david kim james
sqlite lower function
sqlite lower function converts a string into lower-case letters. following is an example −
sqlite> select lower(name) from company;
the above sqlite sql statement will produce the following.
lower(name) ----------- paul allen teddy mark david kim james
sqlite length function
sqlite length function returns the length of a string. following is an example −
sqlite> select name, length(name) from company;
the above sqlite sql statement will produce the following.
name length(name) ---------- ------------ paul 4 allen 5 teddy 5 mark 4 david 5 kim 3 james 5
sqlite sqlite_version function
sqlite sqlite_version function returns the version of the sqlite library. following is an example −
sqlite> select sqlite_version() as 'sqlite version';
the above sqlite sql statement will produce the following.
sqlite version -------------- 3.6.20