将字符串与表中的数据进行比较

Question

假设我有一个输入字符串，像这样：HÊLLÕ WÖRLЩ !!

还有一张名为REPLACE_CHAR_TAB的表，其中包含以下数据:

SPECIAL_ASCII     SPECIAL_CHAR     REPLACE_ASCII     REPLACE_CHAR
-----------------------------------------------------------------
          169                ©              NULL             NULL
          202                Ê                69                E
          208                Ð                68                D
          213                Õ                79                O

现在，我想用这个表中的数据验证输入字符串，并且每当在此表中找到字符串中的特殊字符时，输入字符串中的字符都将被替换为该表中的REPLACE_CHAR。

例如：

Input: HÊLLÕ WÖRLЩ !!
Output: HELLO WORLD !!

我刚刚接触PL/SQL，可以请你帮助和提供一些提示吗？

非常感谢您的指导和帮助！谢谢！

- Law

---

https://dev59.com/lF7Va4cB1Zd3GeqPLaDa - Praveen Prasannan

Answer 1

您可以使用 Oracle ASCII函数 计算字符串中每个字符的ASCII值，但是您的设计对性能有一些危险。

相反，我建议您使用 TRANSLATE。看看这个：

SELECT TRANSLATE('HÊLLÕ WÖRLЩ', '©ÊÐÖÕ', ' EDOO') 
FROM DUAL;

祝你好运！

- David Isla

---

感谢您的指导。然而，根据我的需求，我需要查看表字典（replace_char_tab）。此外，实际上有一些特殊字符：æ，替换字符为ae。在这种情况下，替换字符实际上是2个字符，我认为TRANSLATE无法产生准确的结果？ - Law

Answer 2

尝试提供一个解决方案，以满足您的具体需求：给定一个包含特殊字符和对应普通字符映射的表，我们需要编写PL/SQL来完成这些映射。

1. 创建映射表。

create table replace_char_tab(special_ascii number, special_char nvarchar2(1), 
                              replace_ascii number, replace_char varchar2(1));
insert into replace_char_tab values(49833,'©', null, null);
insert into replace_char_tab values(50058,'Ê', 69, 'E');
insert into replace_char_tab values(50064,'Ð', 68, 'D');
insert into replace_char_tab values(50070,'Ö', 79, 'O');
insert into replace_char_tab values(50069,'Õ', 79, 'O');
commit;

在上述语句中，special_ascii 的值更大，因为 Oracle 的 ascii 原生支持 0-127 个字符。对于更高的范围，根据数据库的字符编码返回 Unicode 值。

2. 创建一个 PL/SQL

create or replace function getSpecialCharMapping(input_string varchar2)
return varchar2
as
     TYPE key_pair_type IS TABLE OF number INDEX BY pls_integer;
     special_rep key_pair_type;
     output_string varchar2(4000);
     r_char nvarchar2(1);
begin
   -- Generate associative array
   for rec in (select * from replace_char_tab) loop
      if rec.replace_ascii is not null then
         special_rep(rec.special_ascii) := rec.replace_ascii;
      else
         special_rep(rec.special_ascii) := 0;
      end if;
   end loop;
   -- Now find map the special chars with ascii
   for cnt in 1..length(input_string) loop
      if special_rep.exists(ascii(substr(input_string, cnt, 1))) then
            output_string := output_string 
            || chr(special_rep(ascii(substr(input_string,cnt,1))));
      else
         output_string := output_string || substr(input_string,cnt,1);
      end if;
    end loop;
    return output_string;
end;
/

在上述PL/SQL中，我们使用了PL/SQL的关联数组功能。它本质上是一个键值对，有助于快速查找。通过使用关联数组，我们试图避免为每个字符映射查询表。

3. 样例执行

select getSpecialCharMapping('HÊLLÕ WÖRLЩ !!') from dual;

这个解决方案的目的是使特殊字符映射具有可扩展性和易于理解性。

链接到 SQLFiddle。

- bprasanna

Answer 3

Oracle提供了TRANSLATE函数来替换单个字符。因此，使用LISTAGG获取转换表中的字符，并使用生成的字符串进行转换：

select translate('HÊLLÕ WÖRLЩ !!', specials, replacers)
from 
(
  select 
    listagg(special_char,'') within group 
      (order by replace_ascii, special_ascii) as specials,
    listagg(replace_char,'') within group 
      (order by replace_ascii, special_ascii) as replacers
  from replace_char_tab
);

SQL fiddle: http://www.sqlfiddle.com/#!4/f4d80/1。

(顺便提一下，按照replace_ascii排序非常重要，这样你就可以得到最后的null值。因此，TRANSLATE删除了相应的字符。按不同的顺序会导致两个字符串的位置不匹配。)

---

如果您想替换多个字符，例如将æ替换为ae，正如您在评论中所说，您不能再使用TRANSLATE。相反，您需要使用REPLACE递归地运行您的翻译表，然后保留最后修改的字符串。为了访问一个接一个的翻译表记录，您必须首先对记录进行编号，以便始终获得下一个记录。

with numbered as
(
  select /*+ materialize */
    replace_char_tab.*,
    row_number() over (order by replace_ascii, special_ascii) as num 
  from replace_char_tab  
)
, replaced(str, num) as
(
  select 'HÊLLÕ WÖRLЩ !!' as str, 0 as num from dual
  union all
  select
    replace(replaced.str, numbered.special_char, numbered.replace_char) as str,
    numbered.num
  from replaced 
  join numbered on numbered.num = replaced.num + 1
)
select max(str) keep (dense_rank last order by num) from replaced;

SQL Fiddle：http://www.sqlfiddle.com/#!4/aa65d7/3。

（该网站与编程有关，可帮助用户在不同的数据库环境中测试和分享SQL查询。）

- Thorsten Kettner

---

我注意到使用了LISTAGG和TRANSLATE。然而，在special_char_tab中它包含像special_character: æ和replace_character: ae这样的内容。在这种情况下，replace_character有2个字符，因此我认为LISTAGG和TRANSLATE解决方案将无法工作.. 嗯.. - Law

你说得对，不能使用 TRANSLATE。我已经编辑了我的答案，展示了如何使用 REPLACE 迭代你的记录。 - Thorsten Kettner