SQLite | SQLite Commands | Change the SELECT Result Separator (.separator)

Tags:

The .separator command changes the default separator used to display SELECT results. This article explains how to use it.

Using the .separator Command

The separator distinguishes values in query output and fields read by the .import command.

.separator COL? ROW?

The first argument sets the column separator. The optional second argument sets the row separator. The default column separator is |, and a separator may contain one or several characters. Use .show to inspect the current settings.

sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: mydb.sqlite3

With the default setting, values are separated by |.

sqlite> select * from user;
1|devkuma|Seoul
2|kimkc|Busan
3|araikuma|Seoul
4|happykuma|Seoul
5|mykuma|Daejeon

Change the separator to a comma and run the query again.

sqlite> .separator ,
sqlite> select * from user;
1,devkuma,Seoul
2,kimkc,Busan
3,araikuma,Seoul
4,happykuma,Seoul
5,mykuma,Daejeon

A multi-character string can also be used.

sqlite> .separator /-/
sqlite> select * from user;
1/-/devkuma/-/Seoul
2/-/kimkc/-/Busan
3/-/araikuma/-/Seoul
4/-/happykuma/-/Seoul
5/-/mykuma/-/Daejeon

If the separator contains spaces, enclose the entire string in double quotation marks.

sqlite> .separator ", "
sqlite> select * from user;
1, devkuma, Seoul
2, kimkc, Busan
3, araikuma, Seoul
4, happykuma, Seoul
5, mykuma, Daejeon