Nul Characters in Strings in SQLite
This technical article explains how SQLite handles NUL characters within string values, noting that standard functions often truncate data at the first NUL. It provides examples of how this behavior can lead to unexpected query results and suggests methods for detecting embedded NULs.
Why it matters
Developers working with database systems need to understand how character encoding and null termination affect data integrity and query accuracy.
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.
CREATE TABLE t1( a INTEGER PRIMARY KEY, b TEXT ); INSERT INTO t1(a,b) VALUES(1, 'abc'||char(0)||'xyz'); SELECT a, b, length(b) FROM t1; The SELECT statement above shows output of:
Get smarter about the news
Sign up free for a feed built around what you actually care about, Dive Deeper research on any story, and the full text of every article.
Create free accountAlready have an account? Sign in