The log10() function in SQLite is used to calculate the base-10 logarithm of a number. This function is part of SQLite’s built-in mathematical functions, and it returns the logarithmic value of the argument passed to it.

Syntax

log10(X)

Where X is the number for which you want to calculate the base-10 logarithm. The number must be positive (greater than 0).

Example

Here’s a simple example to demonstrate:

SELECT log10(1000);

Result:

+-------------+
| log10(1000) |
+-------------+
| 3.0         |
+-------------+

Explanation: The base-10 logarithm of 1000 is 3 because 10 raised to the power of 3 = 1000.

Rounding

Depending on the number we provide, the result could return a large fractional component. We can use the ROUND() function to round the result if we wish.

Example:

SELECT log10(50) unrounded,
       round(log10(50), 2) rounded;

Result:

+------------------+---------+
|    unrounded     | rounded |
+------------------+---------+
| 1.69897000433602 | 1.7     |
+------------------+---------+

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 log10(-50),
       log10(0);

Result:

+------------+----------+
| log10(-50) | log10(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 log10('Ten');

Result:

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

The log10() function is useful in many mathematical, scientific, and engineering applications where base-10 logarithms are needed.