In SQLite, if you need to replace NULL results with text such as “N/A”, “None”, or even the text “NULL”, you can use one of the three solutions below.

Sample Data

First, here’s an example of a query that returns some NULL values.

SELECT 
  CustomerId, 
  Fax  
FROM Customer
LIMIT 5;

Result:

CustomerId  Fax          
----------  -------------
1           +55 (12) 3923
2                        
3                        
4                        
5           +420 2 4172 5

Notice that rows 2, 3, and 4 contain NULL values in the Fax column.

We can use the methods below to change the result so that the NULL columns display “N/A”.

The ifnull() Function

The ifnull() function accepts two arguments, and returns the first one that’s not NULL. If both arguments are NULL, then it returns NULL.

So, the first argument should be the value that may or may not be nullable, and the second argument should be the value that you want to replace any NULL values with.

Here’s how we can modify the previous query to display “N/A” wherever the Fax column is NULL.

SELECT 
  CustomerId, 
  ifnull(Fax, 'N/A') AS Fax
FROM Customer
LIMIT 5;

Result:

CustomerId  Fax          
----------  -------------
1           +55 (12) 3923
2           N/A          
3           N/A          
4           N/A          
5           +420 2 4172 5

The coalesce() Function

The coalesce() function is very similar to the ifnull() function. The difference is that coalesce() accepts more than two arguments. It simply returns the first non-NULL argument.

Therefore, we can use it in exactly the same way we use ifnull() by providing two arguments.

SELECT 
  CustomerId, 
  coalesce(Fax, 'N/A') AS Fax
FROM Customer
LIMIT 5;

Result:

CustomerId  Fax          
----------  -------------
1           +55 (12) 3923
2           N/A          
3           N/A          
4           N/A          
5           +420 2 4172 5

The .nullvalue Dot Command

When using the SQLite command line interface, you can use the .nullvalue dot command to save yourself from having to use one of the previous functions each time you do a query.

When using this command, you provide a string that will be used to replace NULL values.

Example:

.nullvalue N/A

Now, whenever you run a query that returns NULL values, the string “N/A” will replace those NULL values.

SELECT 
  CustomerId, 
  Fax  
FROM Customer 
LIMIT 5;

Result:

CustomerId  Fax          
----------  -------------
1           +55 (12) 3923
2           N/A          
3           N/A          
4           N/A          
5           +420 2 4172 5