SQLite define output field separator on a single line command

Question

I need to use a single command line to fetch a set of records from a database. If I do this:

$ sqlite3 con.db "SELECT name,cell,email FROM contacts"

I get output with a separator "|", where the output looks like this:

Alison|+12345678|alison@mail.com
Ben|+23456789|ben@mail.com
Steve|+34567890|steve@mail.com

Is there a way (in single command line format like specified above) to change the output field separator to something else, like ";;;" or something else or more unique. This is because the output occasionally get the character "|" inside the records, and it causes issues.

My desired result is:

Alison;;;+12345678;;;alison@mail.com
Ben;;;+23456789;;;ben@mail.com
Steve;;;+34567890;;;steve@mail.com

Or any other unique separator, which is not likely to be found inside the values.

(The command is executed on a Linux machine)

Thank you.

score 15 · Accepted Answer · answered Jan 21 '13 at 21:59

15

The -separator option does what you want:

sqlite3 -separator ';;;' con.db "SELECT ..."

The only way to format the output so that you are guaranteed to not get the separator in the values is to quote all strings:

sqlite3 con.db "SELECT quote(name), quote(cell), quote(email) FROM contacts"

However, this would require you to parse the output according to the SQL syntax rules.

answered Jan 21 '13 at 21:59

CL.

173,858
17
217
259

Thanks, exactly what I needed! The first option did the trick nicely. – Sazzy Jan 21 '13 at 22:24
Thank you, the SELECT quote(blah) helped me! – Vada Poché May 22 '17 at 00:58

SQLite define output field separator on a single line command

1 Answers1