Documentation Source Text

</ul>

<p>The dot-commands
are interpreted by the sqlite3.exe command-line program, not by
SQLite itself.  So none of the dot-commands will work as an argument
to SQLite interfaces like [sqlite3_prepare()] or [sqlite3_exec()].

<tcl>hd_fragment dotmode</tcl>
<h1>Changing Output Formats</h1>

<p>The sqlite3 program is able to show the results of a query
in eight different formats: "csv", "column", "html", "insert",
"line", "list", "quote", "tabs", and "tcl".
You can use the ".mode" dot command to switch between these output
formats.</p>
................................................................................
sqlite>
}</tclscript>

<p>The next ".mode" command will reset the ".separator" back to its default.
So you will need repeat the ".separator" command whenever you change
modes if you want to continue using a non-standard separator.




<p>In "quote" mode, the output is formatted as SQL literals.  Strings are
enclosed in single-quotes and internal single-quotes are escaped by doubling.
Blobs are displayed in hexadecimal blob literal notation (Ex: x'abcd').
Numbers are displayed as ASCII text and NULL values are shown as "NULL".
All columns are separated from each other by a comma (or whatever alternative
character is selected using ".separator").

</ul>

<p>The dot-commands
are interpreted by the sqlite3.exe command-line program, not by
SQLite itself.  So none of the dot-commands will work as an argument
to SQLite interfaces like [sqlite3_prepare()] or [sqlite3_exec()].

<tcl>hd_fragment dotmode {.mode}</tcl>
<h1>Changing Output Formats</h1>

<p>The sqlite3 program is able to show the results of a query
in eight different formats: "csv", "column", "html", "insert",
"line", "list", "quote", "tabs", and "tcl".
You can use the ".mode" dot command to switch between these output
formats.</p>
................................................................................
sqlite>
}</tclscript>

<p>The next ".mode" command will reset the ".separator" back to its default.
So you will need repeat the ".separator" command whenever you change
modes if you want to continue using a non-standard separator.

<tcl>
hd_fragment dotmodequote {.mode quote}
</tcl>
<p>In "quote" mode, the output is formatted as SQL literals.  Strings are
enclosed in single-quotes and internal single-quotes are escaped by doubling.
Blobs are displayed in hexadecimal blob literal notation (Ex: x'abcd').
Numbers are displayed as ASCII text and NULL values are shown as "NULL".
All columns are separated from each other by a comma (or whatever alternative
character is selected using ".separator").

<title>NUL Characters In Strings</title>
<tcl>
hd_keywords {NUL characters in strings}
</tcl>

<fancy_format>

<h1>Introduction</h1>

<p>
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:

<ol>
<li><p>
The [length() SQL function] only counts characters up through and excluding
the first NUL.


<li><p>
The [quote() SQL function] only shows characters up through and excluding
the first NUL.

<li><p>
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.
</ol>

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

<h1>Unexpected Behavior</h1>

<p>
Consider the following SQL:

<codeblock>
  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;
</codeblock>

<p>
The SELECT statement above shows output of:

<codeblock>
  1,'abc',3
</codeblock>

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

<codeblock>
  SELECT * FROM t1 WHERE b='abc';
</codeblock>

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

<h1>How To Tell If You Have NUL Characters In Your Strings</h1>

<p>
If you [CAST] a string into a BLOB, then the entire length of the
string is shown.  For example:

<codeblock>
  SELECT a, CAST(b AS BLOB) FROM t1;
</codeblock>

<p>
Gives this result:

<codeblock>
  1,X'6162630078797a'
</codeblock>

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

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

<codeblock>
   instr(X,char(0))
</codeblock>

<p>
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
fo rows that contain embedded NUL characters:

<codeblock>
   SELECT count(*) FROM t1 WHERE instr(b,char(0))>0;
</codeblock>

<h1>Removing NUL Characters From A Text Field</h1>

<p>
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:

<codeblock>
  UPDATE t1 SET b=substr(b,1,instr(b,char(0)))
   WHERE instr(b,char(0));
</codeblock>

it does in MySQL.  This often causes confusion for people who
initially learned SQL on MySQL and then start using SQLite, and
expect the two systems to work identically.

<p>See the [AUTOINCREMENT|SQLite AUTOINCREMENT documentation] for
detailed instructions on what AUTOINCREMENT does and does not do
in SQLite.

it does in MySQL.  This often causes confusion for people who
initially learned SQL on MySQL and then start using SQLite, and
expect the two systems to work identically.

<p>See the [AUTOINCREMENT|SQLite AUTOINCREMENT documentation] for
detailed instructions on what AUTOINCREMENT does and does not do
in SQLite.

<h1>NUL Characters Are Allowed In Text Strings</h1>

<p>NUL characters (ASCII code 0x00 and Unicode \u0000) may appear in
the middle of strings in SQLite.  This can lead to unexpected behavior.
See the "[NUL characters in strings]" document for further information.