当数据包含逗号时将 sql 假脱机为 CSV
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15441170/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Spooling sql to a CSV when data contains commas
提问by user2175401
I'm trying to spool a sql query to a CSV file using a command prompt, however one column of data I'm returning contains comma's in the data and is causing the data to go into the next column. Is there a way to get around this? Ideally, I would like this particular column returned in the middle of query, not at the end.
我正在尝试使用命令提示符将 sql 查询假脱机到 CSV 文件，但是我返回的一列数据在数据中包含逗号，并导致数据进入下一列。有没有办法解决这个问题？理想情况下，我希望在查询中间而不是最后返回此特定列。
I'm 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 the set colsep ',' command is the issue however, haven't been able to figure out what to replace it with. Does anyone have any recommendations?
我知道 set colsep ',' 命令是问题所在，但是一直无法弄清楚用什么来替换它。有人有什么建议吗？
Also, right before my sql query, I'm using a select statement to pull the header information. Select 'a, b, c' from dual; Not sure if that makes a difference in the answer or not.
另外，就在我的 sql 查询之前，我正在使用 select 语句来提取标题信息。从双中选择'a，b，c'；不确定这是否会对答案产生影响。
回答by Wilduck
Two potential answers. Neither of them perfect. If you have commas in some of the results, but no pipes (|), you could switch to a pipe-delimited with
两个可能的答案。他们都不是完美的。如果某些结果中有逗号，但没有竖线 ( |)，则可以切换到竖线分隔的
set colsep '|'
Most software that can read csvwill do just fine with that format.
大多数可以读取的软件都可以csv使用该格式。
If that doesn't work for you, you can realize that to treat commas within a column, you'll need to wrap every data item in quotes:
如果这对您不起作用，您可以意识到要处理列中的逗号，您需要将每个数据项括在引号中：
"data 1","data 2","data,with,commas"
To do this, each separator will need to be ","so you can
要做到这一点，每个分隔符都需要","这样你才能
set colsep '","'
This will not have quotation marks at the beginning and ending of each line, so you can then wrap every line in quotes with sed:
这在每行的开头和结尾不会有引号，因此您可以将每一行用引号括起来sed：
sed 's/^[^$]*$/"&"/'
回答by user3600179
You can modify your select query like
您可以修改您的选择查询，如
select REPLACE(column_name, ',',' ') column name from table_name
This will replace comma value from your column data with space.
这将用空格替换列数据中的逗号值。
回答by BellevueBob
You can modify your query that returns the result set by surrounding that column with double-quotes. Assuming bis the culprit:
您可以通过用双引号将该列括起来来修改返回结果集的查询。假设b是罪魁祸首：
select a
, '"' || trim(B) || '"' as b
, c
from your_table;
Proper syntax depends on your RDBMS version of course.
正确的语法当然取决于您的 RDBMS 版本。
