Home
Menu
Menu)
About
Documentation
Download
License
Support
Purchase
Search
About
Documentation
Download
Support
Purchase
1. Introduction
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 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 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 .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.
2. Unexpected Behavior
Consider the following SQL:
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.
3. How To Tell If You Have NUL Characters In Your Strings
If you CAST a string into a BLOB, then the entire length of the
string is shown. For example:
Gives this result:
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:
4. Removing NUL Characters From A Text Field
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