Documentation Source Text

Check-in [95afd54c2e]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Enhanced documentation for the ".separator" and ".mode quote" commands in the CLI.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 95afd54c2e89c7b4e135596725c166bf58db8a47
User & Date: drh 2017-02-14 20:18:03
Context
2017-02-15
22:43
Correct the rollback journal checksum algorithm description. check-in: b69eaea914 user: drh tags: trunk
2017-02-14
20:18
Enhanced documentation for the ".separator" and ".mode quote" commands in the CLI. check-in: 95afd54c2e user: drh tags: trunk
2017-02-13
16:13
Update dates and tags for the 3.17.0 release. Also fix a hyperlink target in the testing.html document. check-in: 60bb99911d user: drh tags: trunk, release, version-3.17.0
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/cli.in.

176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
...
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
...
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278



















279
280
281
282
283
284
285
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         ascii    Columns/rows delimited by 0x1F and 0x1E
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator strings
                         quote    Escape answers as for SQL
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.once FILENAME         Output for the next SQL command only to FILENAME
.open ?--new? ?FILE?   Close existing database and reopen FILE
                         The --new starts with an empty file
................................................................................
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", "tabs", and "tcl".
You can use the ".mode" dot command to switch between these output
formats.</p>

<p>The default output mode is "list".  In
list mode, each record of a query result is written on one line of
output and each column within that record is separated by a specific
separator string.  The default separator is a pipe symbol ("|").
................................................................................
sqlite> (((.mode list)))
sqlite> (((select * from tbl1;)))
hello|10
goodbye|20
sqlite>
}</tclscript>

<p>You can use the ".separator" dot command to change the separator
for list mode.  For example, to change the separator to a comma and
a space, you could do this:</p>

<tclscript>DisplayCode {
sqlite> (((.separator ", ")))
sqlite> (((select * from tbl1;)))
hello, 10
goodbye, 20



















sqlite>
}</tclscript>

<p>In "line" mode, each column in a row of the database
is shown on a line by itself.  Each line consists of the column
name, an equal sign and the column data.  Successive records are
separated by a blank line.  Here is an example of line mode







|







 







|







 







|
|







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
...
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
...
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         ascii    Columns/rows delimited by 0x1F and 0x1E
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by "|"
                         quote    Escape answers as for SQL
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.once FILENAME         Output for the next SQL command only to FILENAME
.open ?--new? ?FILE?   Close existing database and reopen FILE
                         The --new starts with an empty file
................................................................................
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>

<p>The default output mode is "list".  In
list mode, each record of a query result is written on one line of
output and each column within that record is separated by a specific
separator string.  The default separator is a pipe symbol ("|").
................................................................................
sqlite> (((.mode list)))
sqlite> (((select * from tbl1;)))
hello|10
goodbye|20
sqlite>
}</tclscript>

<p>You can use the ".separator" dot command to change the separator.
For example, to change the separator to a comma and
a space, you could do this:</p>

<tclscript>DisplayCode {
sqlite> (((.separator ", ")))
sqlite> (((select * from tbl1;)))
hello, 10
goodbye, 20
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 within internal single-quotes 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").

<tclscript>DisplayCode {
sqlite> (((.mode quote)))
sqlite> (((select * from tbl1;)))
'hello',10
'goodbye',20
sqlite>
}</tclscript>

<p>In "line" mode, each column in a row of the database
is shown on a line by itself.  Each line consists of the column
name, an equal sign and the column data.  Successive records are
separated by a blank line.  Here is an example of line mode