Oracle 中的假脱机为列添加了空格......我该如何避免它？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33633136/
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 in Oracle adds spaces to columns... how can I avoid it?
提问by toofast
I'm saving the results of a query in a csv file but unwanted spaces are added to some of the fields, when the original data in the database does not contain them.
我将查询的结果保存在一个 csv 文件中，但是当数据库中的原始数据不包含不需要的空格时，会在某些字段中添加不需要的空格。
For example, if one of the rows in the DB has the values "how", "are" and "you", what I get in the file after spooling is a line like :
例如，如果数据库中的行之一具有值“how”、“are”和“you”，则假脱机后我在文件中得到的一行是：
"how | are |you "
"how | are |you "
(the editor doesn't let me write more spaces, but you can imagine there are plenty of them)
（编辑不让我写更多的空间，但你可以想象有很多）
When I just want it to be :
当我只想它是：
"how|are|you"
"how|are|you"
I've tried several setting options with no result. Is there a way to avoid these spaces? Thanks in advance!
我尝试了几个设置选项，但没有结果。有没有办法避免这些空间？提前致谢！
What I got so far:
到目前为止我得到了什么：
SET ECHO OFF;
SET NEWP 0 SPACE 0 PAGES 0 FEED OFF HEAD OFF TRIMS OFF TRIM OFF TAB OFF;
set colsep '|';
set lines 130;
spool myfile.csv
SELECT * FROM SOME_TABLE;
spool off;
This goes inside a call to sqlplus.
这在对 sqlplus 的调用中进行。
采纳答案by William Robertson
Thanks for the update. So you're trying something like this:
感谢更新。所以你正在尝试这样的事情：
set colsep "|"
with demo as
( select 'Various' as col1, 'test' as col2, 'examples' as col3 from dual
union all select 'How', 'are', 'you' from dual )
select col1, col2, col3
from demo;
COL1 |COL2|COL3
-------|----|--------
Various|test|examples
How |are |you
Unfortunately SQL*Plus isn't designed for unloading data like this - the separator is strictly for columns. You'll have to concatenate the values with pipes yourself e.g. col1 || '|' || col2 || '|' || col3(and watch out for column formatting e.g. dates).
不幸的是，SQL*Plus 不是为卸载这样的数据而设计的——分隔符严格用于列。您必须自己将值与管道连接起来，例如col1 || '|' || col2 || '|' || col3（并注意列格式，例如日期）。
回答by Marmite Bomber
Your table contains columns defined as CHAR(nn)which fills the values with blanksto the full length or your application stored the additional blanks along the strings.
你的表包含定义为列CHAR(nn)，其与空白值填充全长或您的应用程序存储沿着琴弦的额外空白。
use rtrimto remove trailing blanks - instaed of
用于rtrim删除尾随空格 - 代替
select c1||'|'||c2||'|'||c3
from tst;
use
用
select rtrim(c1)||'|'||rtrim(c2)||'|'||rtrim(c3)
from tst;
use ltrimas well if you have both leading and trailing blanks
使用ltrim，如果你有两个开头和结尾的空白，以及
select ltrim(rtrim(c1))||'|'||ltrim(rtrim(c2))||'|'||ltrim(rtrim(c3))
from tst;
UPDATE
更新
As pointed out in comment the function trimcan be used instead
正如评论中指出的那样，trim可以使用该函数代替
select trim(c1)||'|'||trim(c2)||'|'||trim(c3)
from tst;
