Refueling sql in CSV when data contains commas

Question

I am trying to start a sql query to a CSV file using the command line, however, one column of data that I return contains a comma in the data and causes the data to go to the next column. Is there any way around this? Ideally, I would like this column to be returned in the middle of the query, and not at the end.

I am currently using the following commands:

set termout off; set colsep ','; set underline off; set feedback off; set pagesize 0; set linesize 1500; set trimspool on; 

I know that the colsep ',' command is a problem, but could not figure out what to replace. Does anyone have any recommendations?

Also, right before my sql query, I use the select statement to pull the header information. Choose "a, b, c" from the double; Not sure if this matters in the answer or not.

Answer 1

Two potential answers. None of them are perfect. If you have commas in some results but no pipes ( | ), you can switch to channelization with

 set colsep '|' 

Most programs that can read csv will do just fine with this format.

If this does not work for you, you can understand that to process commas inside a column, you need to wrap each data item in quotation marks:

 "data 1","data 2","data,with,commas" 

To do this, each delimiter must be "," so you can

 set colsep '","' 

This will not have quotation marks at the beginning and end of each line, so you can wrap each line in quotation marks with sed :

 sed 's/^[^$]*$/"&"/' 

Answer 2

You can change your selection request, for example

 select REPLACE(column_name, ',',' ') column name from table_name 

This will replace the comma value from the column data with a space.

Answer 3

You can modify your query, which returns a result by surrounding this column with double quotes. Assuming b is the culprit:

 select a , '"' || trim(B) || '"' as b , c from your_table; 

The correct syntax, of course, depends on your version of RDBMS.