如何使用 PL/SQL-REGEXP_REPLACE 从字符串末尾删除 0D？

Question

我想在 Oracle 10g 上的 PL/SQL 中使用 REGEXP_REPLACE 来从字符串中删除尾随的 HEX-0D。我从这个问题找到了正则表达式。

在 Perl 中，其内容如下：

$output =~ tr/\x{d}\x{a}//d;  

或者

$output =~ s/\s+\z//;

How can Itranslate this to PL/SQL?

Answer 1

/* PL/SQL */

declare
  in_str constant varchar2(30) := 'foo' || chr(13) || 'bar' || chr(13);
  out_str varchar2(30);
begin
  dbms_output.put_line('in_str = ' || utl_raw.cast_to_raw(in_str));

  select regexp_replace(in_str, chr(13) || '
, '') into out_str from dual;

  dbms_output.put_line('out_str = ' || utl_raw.cast_to_raw(out_str));
end;
/

/* SQL */

select utl_raw.cast_to_raw('foo' || chr(13) || 'bar' || chr(13)) as BEFORE from dual;

select utl_raw.cast_to_raw(
  regexp_replace('foo' || chr(13) || 'bar' || chr(13), chr(13) || '
, '')
) as AFTER from dual;

/* OUTPUT */

Session altered.

in_str = 666F6F0D6261720D
out_str = 666F6F0D626172

PL/SQL procedure successfully completed.

BEFORE
------------------------------------------------------------------------------
666F6F0D6261720D

AFTER
------------------------------------------------------------------------------
666F6F0D626172

Answer 2

您还拥有 RTRIM 功能

select dump(a), dump(rtrim(a,chr(13))) from (select 'test'||chr(13) a from dual);