SQLite has very little support for typical arithmetic functions. In the SQLite contrib-section there is an extension for that by Liam Healy. The description goes:
Provide mathematical and string extension functions for SQL queries using the loadable extensions mechanism.
- Math: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil, floor, pi
- String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter
- Aggregate: stdev, variance, mode, median, lower_quartile, upper_quartile
To ease the installation, I packaged into into a RPM for CentOS 6:
The source-RPM will build quite easily on any RPM-disto. There are no weird dependecies or anything.
Example usage:
# sqlite3
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .load libsqlitefunctions.so
sqlite> select floor(1.9);
1
sqlite> select ceil(2.1);
3
sqlite> select reverse("reverse");
esrever
sqlite> .quit
Now developing apps with SQLite back-end is much easier.
