SQLite define output field separator on a single line command
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
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.