我有一個要求，比如有一個字串，比如“ FATALITY, BODY SYS, TEAM BUILDING EVENT.The EMPLOYEE was Participanting in a Team BUILDING EVEN 當他心臟病發作時。該事件是致命的。 ”我還有另一個查找表匹配上面字串中的某個單詞。如果這句話中有任何單詞匹配，那么匹配的單詞應該只需要回傳其他明智的空閥需要在那里。這可以使用 regexp_substr 完成，但無法匹配模式。
輸入字串：FATALITY, BODY SYS, TEAM BUILDING EVENT 。當他心臟病發作時，該員工正在參加一個團隊建設活動。事件是致命的。
預期的 o/p: HEART,FATAL -- 這顆心臟和致命的將在查找表中。
提前致謝。
uj5u.com熱心網友回復：
一個簡單的INSTR功能怎么樣？
SQL> with string (id, col) as
2 (select 1, 'FATALITY, BODY SYS, TEAM BUILDING EVENT.THE EMPLOYEE WAS PARTICIPATING IN A TEAM BUILDING EVENT WHEN HE SUFFERED A HEART ATTACK. THE EVENT WAS FATAL.' from dual union all
3 select 2, 'THERE IS ONLY HEART IN HERE' from dual),
4 lookup (word) as
5 (select 'HEART' from dual union all
6 select 'FATAL' from dual
7 )
8 select s.id,
9 listagg(l.word, ', ') within group (order by l.word) result
10 from string s join lookup l on instr(s.col, l.word) > 0
11 group by s.id;
ID RESULT
---------- ----------------------------------------
1 FATAL, HEART
2 HEART
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/425014.html
下一篇：如何在sql計算中使用變數