SQLite allows NUL characters (ASCII 0x00, Unicode \u0000) inside text values, but they cause surprising behavior: the length() function counts only up to the first NUL, quote() truncates at the first NUL, and the CLI’s .dump command omits everything after the first NUL. Using NUL in strings is not recommended.

Consider a table with `INSERT INTO t1(a,b) VALUES(1, 'abc'||char(0)||'xyz');`. A `SELECT a, b, length(b) FROM t1;` shows the string as `'abc'` with length 3, yet `WHERE b='abc'` returns no rows because the stored value is actually seven characters long. The CLI output hides the hidden characters, creating apparent bugs.

To detect embedded NULs, cast the column to BLOB: `SELECT a, CAST(b AS BLOB) FROM t1;` reveals the NUL as the fourth byte. An automated check uses `instr(b,char(0))>0`; a non‑zero result gives the position of the first NUL. Count affected rows with `SELECT count(*) FROM t1 WHERE instr(b,char(0))>0;`.

Remove NULs and trailing text via `UPDATE t1 SET b=substr(b,1,instr(b,char(0))) WHERE instr(b,char(0));`. This truncates each value at the first NUL, cleaning the data.