The log2() function in SQLite calculates the base-2 logarithm of a given number.

This function can be useful for scenarios such as binary calculations, information theory, or situations where the base-2 logarithm is needed.

Syntax

log2(X)

Where X is the number for which you want to calculate the base-2 logarithm. It must be a positive number greater than 0.

Example

Let’s calculate the base-2 logarithm of 16:

SELECT log2(16);

Result:

+----------+
| log2(16) |
+----------+
| 4.0      |
+----------+

Explanation: The base-2 logarithm of 16 is 4 because 2 raised to the power of 4 = 16.

Example with ROUND()

If you want to round the result, you can use the ROUND() function in combination with log2().

For example, to calculate the base-2 logarithm of 50 and round the result to 2 decimal places:

SELECT log2(50) AS unrounded,
       ROUND(log2(50), 2) AS rounded;

Result:

+------------------+---------+
|    unrounded     | rounded |
+------------------+---------+
| 5.64385618977472 | 5.64    |
+------------------+---------+

Explanation: The base-2 logarithm of 50 is approximately 5.64385618977472, and after rounding to two decimal places, the result is 5.64.

We Must Pass a Positive Number

The argument X must be a positive real number; passing zero or a negative number will result in NULL.

To demonstrate this, let’s first set a string for null values:

.nullvalue 'null'

I did that because by default null values are returned as an empty string. I set .nullvalue to null in order to explicitly show when a null value is returned.

Anyway, let’s now run the following query:

SELECT log2(-50),
       log2(0);

Result:

+-----------+---------+
| log2(-50) | log2(0) |
+-----------+---------+
| null      | null    |
+-----------+---------+

As expected, we can see that null was returned in both cases.

Passing the Wrong Argument Type

Passing the wrong argument type results in null being returned:

SELECT log2('Ten');

Result:

+-------------+
| log2('Ten') |
+-------------+
| null        |
+-------------+