Article URL: https://sqlite.org/nulinstr.html Comments URL: https://news.ycombinator.com/item?id=48928343 Points: 32 # Comments: 8

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. (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.