By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create table temp(
id NUMBER,
word VARCHAR2(1000),
Sentence VARCHAR2(2000)
);
insert into temp
SELECT 1,'automation testing', 'automtestingation testing is popular kind of testing' FROM DUAL UNION ALL
SELECT 2,'testing','manual testing' FROM DUAL UNION ALL
SELECT 3,'manual testing','this is an old method of testing' FROM DUAL UNION ALL
SELECT 4,'punctuation','automation testing,manual testing,punctuation,automanual testing-testing' FROM DUAL UNION ALL
SELECT 5,'not matched','testing testing testing' FROM DUAL;
5 rows affected
MERGE INTO temp o
USING (
SELECT s_rid,
sentence,
is_last
FROM (
SELECT s.rowid AS s_rid,
w.id AS word_id,
w.word,
CAST(
REGEXP_REPLACE(
REGEXP_REPLACE(
s.sentence,
'(^|\W)' || w.word || '($|\W)',
'\1' || w.id || '\2'
),
'(^|\W)' || w.word || '($|\W)',
'\1' || w.id || '\2'
)
as varchar2(4000)
) sentence,
length(w.word) word_length
FROM temp w
JOIN temp s
ON REGEXP_LIKE(
s.sentence,
'(^|\W)' || w.word || '(\W|$)'
)
)
model
partition by (s_rid)
dimension by (
row_number() over(partition by s_rid order by word_length desc, word) rn
)
measures(word_id, word, sentence, 0 is_last)
rules (
5 rows affected
SELECT * FROM temp
| ID | WORD | SENTENCE |
|---|---|---|
| 1 | automation testing | automtestingation 2 is popular kind of 2 |
| 2 | testing | 3 |
| 3 | manual testing | this is an old method of 2 |
| 4 | punctuation | 1,3,4,automanual 2-2 |
| 5 | not matched | 2 2 2 |