How to display "Null" (SQLite)

Question

On SQLite, I displayed the table "user" as shown below but "Null" is not displayed so I cannot differentiate between "Null" and Blank(Empty String):

sqlite> .header on
sqlite> .mode box
sqlite> select * from user;
┌────┬─────────────────┐
│ id │      name       │
├────┼─────────────────┤
│ 1  │ Steve Jobs      │
│ 2  │                 │ <- Null
│ 3  │                 │ <- Null
│ 4  │ Bill Gates      │
│ 5  │                 │ <- Blank(Empty String)
│ 6  │ Mark Zuckerberg │
└────┴─────────────────┘

Are there any ways to display "Null"?

score 2 · Accepted Answer · edited Feb 09 '23 at 05:21

This command below sets "String" values to "Null" values:

.nullvalue <String>

So, set "Null" as shown below:

.nullvalue Null

Then, "Null" are displayed for "Null" values as shown below:

sqlite> .header on
sqlite> .mode box
sqlite> select * from user;
┌────┬─────────────────┐
│ id │      name       │
├────┼─────────────────┤
│ 1  │ Steve Jobs      │
│ 2  │ Null            │ <- Null
│ 3  │ Null            │ <- Null
│ 4  │ Bill Gates      │
│ 5  │                 │ <- Blank(Empty String)
│ 6  │ Mark Zuckerberg │
└────┴─────────────────┘

Next, set "This is Null." as shown below:

.nullvalue "This is Null."

Then, "This is Null" are displayed for "Null" values as shown below:

sqlite> .header on
sqlite> .mode box
sqlite> select * from user;
┌────┬─────────────────┐
│ id │      name       │
├────┼─────────────────┤
│ 1  │ Steve Jobs      │
│ 2  │ This is Null.   │ <- Null
│ 3  │ This is Null.   │ <- Null
│ 4  │ Bill Gates      │
│ 5  │                 │ <- Blank(Empty String)
│ 6  │ Mark Zuckerberg │
└────┴─────────────────┘

And these commands below show the details of the command ".nullvalue":

.help .nullvalue

Or:

.help nullvalue

Then, this is how it looks like below:

sqlite> .help .nullvalue  
.nullvalue STRING        Use STRING in place of NULL values

How to display "Null" (SQLite)

1 Answers1