How to find power of a number in SQLite

I was strugginling with this too, but if all you need is powers of 2 (or multiples, etc) there is a simpler way:

Use the shift operator, e.g

SELECT 1 << mytable.value 

SELECT 1 << (table.x + etc..)

You can also create an SQLite user-defined function from python. Based on the example at docs.python.org: sqlite3.Connection.create_function

Create a python function:

def sqlite_power(x,n):
    return int(x)**n
print(sqlite_power(2,3))
# 8    

Create a SQLite user-defined function based on the python function:

con = sqlite3.connect(":memory:")
con.create_function("power", 2, sqlite_power)

Use it:

cur = con.cursor()
cur.execute("select power(?,?)", (2,3))
print cur.fetchone()[0]
# 8

https://www.cafe-encounter.net/p3244/installing-and-using-sqlite-extensions-on-macos-and-maybe-windows-linux-too

Step was to build the Math extensions library that some wonderful person named Liam Healy wrote:

Enter following command in terminal :

Step 1) Download/ Open link http://sqlite.org/contrib/download/extension-functions.c?get=25

Step 2) Go to location where extension-functions.c is downloaded. Run command "gcc -fno-common -dynamiclib extension-functions.c -o libsqlitefunctions.dylib". This will create file libsqlitefunctions.dylib at same place then you can use that in your ios application from xcode.

Now in your cocoa app you can add:

“SELECT load_extension(’libsqlitefunctions.dylib’);”

and then you have access to all kinds of glorious methods like COS, SQRT, etc! You can use them in your app like this:

//Activate database loading
sqlite3_enable_load_extension(database, 1);
sqlite3_load_extension(database,”libsqlitefunctions.dylib”,0,0);

SQLite doesn't have a lot of functions available. But the good news is that is easy enough to add your own.

Here's how to do it using the C API (which also works from Objective-C code).

First write a power function:

void sqlite_power(sqlite3_context *context, int argc, sqlite3_value **argv) {
    double num = sqlite3_value_double(argv[0]); // get the first arg to the function
    double exp = sqlite3_value_double(argv[1]); // get the second arg
    double res = pow(num, exp);                 // calculate the result
    sqlite3_result_double(context, res);        // save the result
}

Then you need to register the function:

int res = sqlite3_create_function(dbRef, "POWER", 2, SQLITE_UTF8, NULL, &sqlite_power, NULL, NULL);

The 2 is the number of arguments for the function. dbRef is of course the sqlite3 * database reference.