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

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