When working with the SQLite command line shell, you have the option of having your query results formatted as string literals.

You can do this by switching to “quote” mode. When you do this, strings are enclosed in single-quotes and internal single-quotes are escaped by doubling. Also, blobs are displayed in hexadecimal blob literal notation, numbers are displayed as ASCII text, and NULL values are shown as “NULL”.

If you need to format your results with double quotes, consider using csv mode.

Example

Here’s an example to demonstrate.

.mode quote
SELECT * FROM Products;

Result:

1,'Widget Holder',139.49999999999999999
2,'Widget Opener',89.700000000000002838
3,'Bob''s Best Widget',374.19999999999998862
4,'Blue Widget',63.000000000000000001

Notice that Bob’s Best Widget in the third row has been double-quoted at its internal single-quote.

You’ll also notice that the results are displayed in a comma-separated list.

Note that double-quotes are not affected. Here’s what happens if I update this entry to Bob’s “Best” Widget and run the query again.

UPDATE Products 
SET ProductName = 'Bob''s "Best" Widget' 
WHERE ProductId = 3;
SELECT * FROM Products;

Result:

1,'Widget Holder',139.49999999999999999
2,'Widget Opener',89.700000000000002838
3,'Bob''s "Best" Widget',374.19999999999998862
4,'Blue Widget',63.000000000000000001

Save the Setting

When you open a new connection to SQLite, it will output query results using the default format, which is a pipe-separated list. In other words, your query results won’t be quoted and you’ll need to run .mode quote again each time you connect.

If you don’t want to have to keep doing this every time you connect, you can save your settings in a configuration file.

To do this, enter the following into a text file:

.mode quote

And save that file as .sqliterc in your home directory.

Now, whenever you connect to the SQLite command line shell, it will read that file and use its settings.

If you already have a file, then you might want to simply update it, while keeping any other settings.