Nul Characters in Strings in SQLite(sqlite.org)

SQLite allows NUL characters (ASCII 0x00, Unicode \u0000) in the middle
of string values stored in the database. However, the use of NUL within
strings can lead to surprising behaviors:

The length() SQL function only counts characters up to and excluding
the first NUL.

The quote() SQL function only shows characters up to and excluding
the first NUL.

The .dump command in the CLI omits the first NUL character and all
subsequent text in the SQL output that it generates. In fact, the
CLI omits everything past the first NUL character in all contexts.

The use of NUL characters in SQL text strings is not recommended.

The SELECT statement above shows output of:

(Through this document, we assume that the CLI has ".mode quote" set.)
But if you run:

Then no rows are returned. SQLite knows that the t1.b column actually
holds a 7-character string, and the 7-character string 'abc'||char(0)||'xyz'
is not equal to the 3-character string 'abc', and so no rows are returned.
But a user might be easily confused by this because the CLI output
seems to show that the string has only 3 characters. This seems like
a bug. But it is how SQLite works.

If you CAST a string into a BLOB, then the entire length of the
string is shown. For example:

In the BLOB output, you can clearly see the NUL character as the 4th
character in the 7-character string.

Another, more automated, way
to tell if a string value X contains embedded NUL characters is to
use an expression like this:

If this expression returns a non-zero value N, then there exists an
embedded NUL at the N-th character position. Thus to count the number
of rows that contain embedded NUL characters:

The following example shows how to remove NUL character, and all text
that follows, from a column of a table. So if you have a database file
that contains embedded NULs and you would like to remove them, running
UPDATE statements similar to the following might help:

This page was last updated on 2022-05-23 22:21:54Z