sqlplus 从假脱机中删除 \r \n \t

发布于 2024-09-04 18:09:02 字数 805 浏览 8 评论 0原文

是否有任何 sql*plus 命令可以从输出到假脱机文件的结果集中删除 \r \n 和 \t ？也就是说，“修剪”每条记录？

我们过去使用过set trim on，但它似乎不符合我们现在的需要。我试图避免在sql查询中调用oracle的translate、chr函数。

例如，

set termout off
set spool somefile.dat
set lin  600

select data from mytable;

set spool off;
exit;

我的查询返回这个

|DATA|
|\n \t\t\t\t\t thisistheactualdata \t\t\t\t\t\t\n|

并且我想将其保留在我的假脱机文件

thisistheactualdata

更新

中好吧，我们最终做了这样的事情。

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;

但有一些坏消息：我们需要在 oracle 8 中运行它，而 regexp_replace 似乎不可用。：（

提前致谢。

原文

Is there any sql*plus command to remove \r \n and\t from the result set that's going out to the spool file? That is, "trim" every record?

We've used set trim on in 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.

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. :(

Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

记忆里有你的影子 2024-09-11 18:09:02

我认为您无法使用 SQL*Plus 指令来做到这一点。 SQL*Plus 是一个相当瘦的客户端，其设计目的不是接触结果集本身的数据。

如果您不想使用内置 SQL 函数来修改输出，那么我认为您必须对假脱机文件进行后处理。

由 DCookie 编辑：
由于我错过了 OP 最初提出的基于非 TRANSLATE/CHR 的解决方案的请求（我专注于 OP 的哀叹，他们被 8i 困住了），为了公平起见 dpbradley，我将撤回我的答案并将其包含在其中一，因为它已被明确考虑。这是我写的：

您可以针对您的 Oracle 8 情况尝试 TRANSLATE 函数：
SELECT TRANSLATE(L,'A'||CHR(10)||CHR(9)||CHR(13),'A') FROM test;

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.

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.

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: