When using the SQLite command line interface (CLI), by default, whenever a query returns a null value, the output is the empty string ('').

We can change this by using the -nullvalue command line option when launching SQLite.

We can also use the .nullvalue dot command if we’re already in the SQLite CLI.

The -nullvalue Option

We can use the -nullvalue option when launching SQLite.

Here’s an example:

sqlite3 -nullvalue null

In this case, whenever I encounter a null value, the text null will be returned. I used null in this example, but this could be any text.

Launching SQLite without that option would have set -nullvalue to the empty string.

The .nullvalue Dot Command

If we’re already in SQLite, we don’t need to exit and relaunch in order to change our nullvalue settings. We can use the .nullvalue dot command instead

Example:

.nullvalue null

Just to be clear, that needs to be run from within the SQLite command shell (i.e. after you’ve already launched SQLite).

Again, we can use any text here to represent null values.

Example

Let’s set nullvalue while launching SQLite:

sqlite3 -nullvalue n/a

Now, whenever a null value is returned, the string n/a will be output.

Let’s do that:

SELECT 1/0;

Result:

+-----+
| 1/0 |
+-----+
| n/a |
+-----+

Dividing a number by zero returns a null value. In our case, we get n/a, which is exactly as expected.

Now let’s change our nullvalue settings:

.nullvalue null

And run the query again:

SELECT 1/0;

Result:

+------+
| 1/0  |
+------+
| null |
+------+

Now we get the string null whenever a null value is returned.

The Default Setting

Suppose we launch SQLite without setting nullvalue:

sqlite3

Here, we open up our computer’s terminal or command prompt and enter the command to launch SQLite (i.e. sqlite3) without specifying any options.

Now, when we run a query that returns a null value, we get the empty string:

SELECT 1/0;

Result:

+-----+
| 1/0 |
+-----+
|     |
+-----+

That’s the default result for null values. It’s what we get when we don’t use the -nullvalue option or .nullvalue dot command.