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|[email protected]
Ben|+23456789|[email protected]
Steve|+34567890|[email protected]

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;;;[email protected]
Ben;;;+23456789;;;[email protected]
Steve;;;+34567890;;;[email protected]

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.

Answer 1

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.