How to escape special characters in string while performing inner join in Oracle?

Question

There is a temp table with words and id's. I have to inner join those words with the main table to get their id's. But the temp table might have special characters along with the words like digits. / .digits / ,digits / digits, / -digits / digits-. In this case, the inner join would not return the value digits. How to escape these characters to make sure the value "digits" is returned?

I need to retain the special characters in the tmp table. Just need to escape those while joining.

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');

The expected outcome is to have word_id'd updated to tmp table from main_table.

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

Answer 1

You may try using REGEXP_REPLACE to remove any non alphanumeric characters before doing the comparison:

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);