Documentation Source Text

  function and the printf() function from the standard C library.)^
  The first argument is a format string that specifies how to construct the output
  string using values taken from subsequent arguments.  ^If the FORMAT argument is
  missing or NULL then the result is NULL.  ^The %n format is silently ignored and
  does not consume an argument.  ^The %p format is an alias for %X.  ^The %z format
  is interchangeable with %s.  ^(If there are too few arguments in the argument list,
  missing arguments are assumed to have a NULL value, which is translated into
  0 or 0.0 for numeric formats or an empty string for %s.)^  See the
  [built-in printf()] documentation for additional information.
}
  

funcdef {quote(X)} {} {
  ^The quote(X) function returns the text of an SQL literal which
  is the value of its argument suitable for inclusion into an SQL statement.
  ^Strings are surrounded by single-quotes with escapes on interior quotes

<title>SQLite's Built-in printf()</title>
<tcl>hd_keywords {built-in printf()}</tcl>
<table_of_contents>

<h1>Overview</h1>

<p>SQLite contains its own implementation of the string formatting routine "printf()",
accessible via the following interfaces:

<p>The same core string formatter is also used internally by SQLite.

<h2>Advantages</h2>

<p>Why does SQLite have its own private built-in printf() implementation?
Why not use the printf() implementation from the standard C library?

Several reasons:

<p>
<ol>
<li><p>
By using its own built-in implementation, SQLite guarantees that the
output will be the same on all platforms and in all LOCALEs.
This is important for consistency and for testing.  It would be problematic
if one machine gave and answer of "5.25e+08" and another gave an answer
of "5.250e+008".  Both answers are correct, but it is better when SQLite
always gives the same answer.

<li><p>
We know of no way to use the standard library printf() C interface to
implement the [printf() SQL function] feature of SQLite.  The built-in
printf() implementation can be easily adapted to that task, however.

<li><p>
The printf() in SQLite supports new non-standard substitution
types ([%q], [%Q], [%w], and [%z]) that are useful both internally to SQLite
and to applications using SQLite.
Standard library printf()s cannot normally be extended in this way.

<li><p>
Via the [sqlite3_mprintf()] and [sqlite3_vmprintf()] interfaces,
the built-in printf() implementation supports the ability to render an
arbitrary-length string into a memory buffer obtained from [sqlite3_malloc64()].
This is safer and less error prone than trying to precompute an upper size
limit on the result string, allocate an appropriately sized buffer, and
then calling snprintf().

<li><p>
The SQLite-specific printf() supports a new flag (!) called the

<p>
In fairness, having a built-in implementation of printf() also comes with
some disadvantages.  To wit:

<ol>
<li><p>
The built-in printf() implementation uses extra code space 
(about 7800 bytes on GCC 5.4 with -Os).

<li><p>
The floating-point to text conversion subfunction for the built-in printf()
is limited in precision to 16 significant digits or 26 significant digits
if the "!" alternate-form-2 flag is used.
Every IEEE-754 double can be represented exactly as a decimal floating-point
value, but some doubles require more than 16 or 26 significant digits.

<li><p>
The order of the buffer pointer and buffer size parameters in the built-in
snprintf() implementation is reversed from the order used in standard-library
implementations.

The other elements of the substitution are optional.

<p>To include a single "%" character in the output, put two consecutive
"%" characters in the template.

<h2>Substitution Types</h2>

<p>The following chart shows the substitution types supported by SQLite:

<center>
<table border=1 cellpadding="10" width="80%">
<tr>
<th>Substitution Type<th>Meaning
<tr>
<td>%

    Lower-case hexadecimal is used for %x and and upper-case is used
    for %X
<tr>
<td>o
<td>The argument is an integer which is displayed in octal.
<tr>
<td>s, z
<td>
<tcl>hd_fragment percentz {%z}</tcl>
    The argument is a zero-terminated string that is displayed.  For
    the %z type in C-language interfaces, [sqlite3_free()] is invoked
    on the string after it has be copied into the output. The %s and %z
    substitutions are identical for the SQL printf() function.<br><br>
    The %s substitution is universal, but
    the %z substitution is an SQLite enhancement, not found in other
    printf() implementations.
<tr>

<td>n
<td>The argument is a pointer to an integer.  Nothing is displayed for
    this substitution type.  Instead, the integer to which the argument
    points is overwritten with the number of characters in the generated
    string that result from all format symbols to the left of the %n.
<tr>
<td>q, Q
<td><tcl>hd_fragment percentq {%q} {%Q}</tcl>
    The argument is a zero-terminated string.  The string is printed with
    all single quote (') characters doubled so that the string can safely
    appear inside an SQL string literal.  The %Q substitution type also
    puts single-quotes on both ends of the substituted string.
    <br><br>If the argument
    to %Q is a null pointer then the output is an unquoted "NULL".  In other
    words, a null pointer generates an SQL NULL, and a non-null pointer generates
    a valid SQL string literal.  If the argument to %q is a null pointer
    then no output is generated.  Thus a null-pointer to %q is the same as
    an empty string.
    <br><br>For these  substitutions, the precision is the number of bytes or
    characters taken from the argument, not the number of bytes or characters that
    are written into the output.
    <br><br>
    The %q and %Q substitutions are SQLite enhancements, not found in
    most other printf() implementations.
<tr>
<td>w
<td><tcl>hd_fragment percentw {%w}</tcl>
    This substitution works like %q except that it doubles all double-quote
    characters (") instead of single-quotes, making the result suitable for
    using with a double-quoted identifier name in an SQL statement.
    <br><br>
    The %w substitution is an SQLite enhancements, not found in
    most other printf() implementations.
</table>
</center>