在 Oracle 中执行内部连接时如何转义字符串中的特殊字符?

Question

有一个带有单词和id的临时表。我必须在内部将这些单词与主表连接起来才能获得他们的 id。但是临时表可能包含特殊字符以及数字等单词。/.数字/，数字/数字，/-数字/数字-。在这种情况下，内部连接不会返回值数字。如何转义这些字符以确保返回值"数字"？

我需要保留 tmp 表中的特殊字符。只需要在加入时逃离这些。

for t in (select id,word from tmp)
LOOP
update tmp a
set a.word_id = (
select b.id
from main_table b
where lower(a.word) = lower(b.word)
and rownum =1
)
where a.word in (select word from tmp where word = t.word);
END LOOP;  

Here is the test data:
CREATE TABLE TMP
(ID NUMBER,
WORD VARCHAR2(4000 BYTE),
WORD_ID NUMBER
);
CREATE TABLE main_table
(ID NUMBER,
WORD VARCHAR2(4000 BYTE));
insert into tmp(id,word) values ( 1, 'digits' );
insert into tmp(id,word) values ( 2, 'digits.' );
insert into tmp(id,word) values ( 3, '.digits-' );
insert into main_table values( 111, 'digits');

预期结果是word_id从main_table更新到 tmp 表。

ID    WORD      WORD_ID
1     digits     111
2     digits.    111
3     .digits-   111

Answer 1

在进行比较之前，您可以尝试使用REGEXP_REPLACE删除任何非字母数字字符：

UPDATE tmp a
SET a.word_id = (SELECT b.id
FROM main_table b
WHERE REGEXP_REPLACE(a.word, '[^A-Za-z0-9]', '') = b.word);

Answer 2

翻译是你的朋友。

简单定义要保留的字符 - 它们在TRANSLATE字符串和应删除的字符中定义相同，它们映射到 NULL。

此查询中提供了此类转换的示例

select word,
translate(lower(word),'abcdefghijklmnopqrstuvwxyz.-#*+',
'abcdefghijklmnopqrstuvwxyz') word_clean
from tmp;
WORD       WORD_CLEAN
---------- ----------
digits     digits    
digits.    digits    
.digits-   digits

联接将如下所示

select tmp.word tmp_word,  main_table.word main_word
from tmp
left outer join  main_table
on translate(lower(tmp.word),'abcdefghijklmnopqrstuvwxyz.-#*+',
'abcdefghijklmnopqrstuvwxyz') = main_table.word

请注意，我正在使用lower来消除大写字母 - 如果需要，请进行调整。

另请注意，TRANSLATE选项将产生比具有REGEXP的选项更好的性能。

Answer 3

一种选择是在UPDATE语句中子选择的WHERE条件下使用regexp_replace()函数中的模式[^[:alnum:]]POSIX模式：

regexp_replace(t.word,'[^[:alnum:]]') = m.word

其中t是表tmp的别名，m是main_table的别名。

Answer 4

下面是另一种方法。

update tmp a
set a.word_id = (
select b.id
from main_table b
where lower(a.word) like concat(concat('%',b.word),'%') 
and rownum =1