oracle sqlplus 从假脱机中删除 \r \n \t
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3006605/
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
sqlplus remove \r \n \t from spool
提问by Tom
Is there any sql*plus command to remove \r\nand\tfrom the result set that's going out to the spool file? That is, "trim" every record?
是否有任何 sql*plus 命令可以从输出到假脱机文件的结果集中删除\r\n和删除\t？也就是说，“修剪”每条记录？
We've used set trim onin the past, but it doesn't seem to bue what we need right now. I'm trying to avoid calling oracle's translate, chr functions in the sql query.
我们过去使用 set trim on过，但它似乎不能满足我们现在的需要。我试图避免在 sql 查询中调用 oracle 的 translate、chr 函数。
For example,
例如，
set termout off
set spool somefile.dat
set lin 600
select data from mytable;
set spool off;
exit;
My query returns this
我的查询返回这个
|DATA|
|\n \t\t\t\t\t thisistheactualdata \t\t\t\t\t\t\n|
And I'd like to keep this in my spooled file
我想把它保存在我的假脱机文件中
thisistheactualdata
update
更新
Well, we ended up doing something like this.
好吧，我们最终做了这样的事情。
set tab off;
spool /home/oracle/out.dat
set linesize 20
set termout off
set trim on
select regexp_replace(l,'(\t|\n)','') from test;
spool off;
exit;
But got some bad news: We need to run this in oracle 8, and regexp_replace doesn't seem to be available. :(
但是得到了一些坏消息：我们需要在 oracle 8 中运行它，而 regexp_replace 似乎不可用。:(
Thanks in advance.
提前致谢。
采纳答案by dpbradley
I don't think you're going to be able to do this with a SQL*Plus directive. SQL*Plus is a pretty thin client and isn't designed to touch the data from the result set itself.
我不认为您将能够使用 SQL*Plus 指令来做到这一点。SQL*Plus 是一个非常瘦的客户端，并不是为了接触结果集本身的数据而设计的。
If you don't want to use the built-in SQL functions to modify the output then I think you're stuck with post-processing a spooled file.
如果您不想使用内置 SQL 函数来修改输出，那么我认为您必须对假脱机文件进行后期处理。
EDIT by DCookie: Since I missed the OP's original request for a non-TRANSLATE/CHR based solution (I focused on the OP's lament that they were stuck with 8i), in fairness to dpbradley I'm going to withdraw my answer and include it as part of this one, since it was clearly considered. Here's what I wrote:
DCookie 编辑：由于我错过了 OP 对基于非 TRANSLATE/CHR 的解决方案的原始请求（我专注于 OP 对他们坚持使用 8i 的哀叹），为了对 dpbradley 公平，我将撤回我的答案并将其包括在内作为这个的一部分，因为它被明确考虑过。这是我写的：
You might try the TRANSLATE function for your Oracle 8 situation:
SELECT TRANSLATE(L,'A'||CHR(10)||CHR(9)||CHR(13),'A') FROM test;
您可以针对您的 Oracle 8 情况尝试 TRANSLATE 函数：
SELECT TRANSLATE(L,'A'||CHR(10)||CHR(9)||CHR(13),'A') FROM test;
回答by Mark Baker
Without trying it, does
不尝试，确实
SET TAB OFF
have the desired effect?
有想要的效果吗？
