This article presents two ways to insert a new line character into a string in SQLite.

This means you can have some text on one line, more text on another line, etc, rather than it being one long line.

The char() Function

You can use the char() function with an argument of 10 (or 13 depending on your operating system) to insert a new line character into a string.

Here’s a basic example to demonstrate.

SELECT char(65,66,10,67,68);

Result:

AB
CD

In this case I used the char() function for all characters, including the new line character 10.

Concatenation

Probably a more common usage is to concatenate the new line character with other strings. For example, you could use char(10) whenever you need a new line.

SELECT 'Apples' || char(10) || 'Carrots';

Result:

Apples
Carrots

Use a BLOB Literal

BLOB literals are string literals containing hexadecimal data and preceded by a single x or X character.

You can therefore use the hexadecimal equivalent of a new line via a BLOB literal to insert a new line into your strings.

Here’s the previous example again, except this time I use a BLOB literal for the new line instead of char(10).

SELECT 'Apples' || x'0a' || 'Carrots';

Result:

Apples
Carrots

If you find x'0a' doesn’t work on your system, perhaps try x'0D'.