sql - 使用 SQL 搜索字符串中出现次数最多的值

Question

所以如果你有这样一个字符串:

"lorum ipsum testing with some dummy text lorum woop what testing again."

你有一个像这样的数据库表:

ID |   TESTING
---------------
1  |   dummy
2  |   lorum
3  |   trol
4  |   haha

如何仅使用 sql 检查 string 中出现次数最多的值。

所以在这种情况下它会返回:2 |洛鲁姆。

起初我认为 LOCATE() 函数会很有用。我试过例如:

SELECT *, LOCATE(testing, "<<string comes here>>") FROM <table>

这可能吗？如果不是最好的方法是什么？

Answer 1

在 Oracle 中，您可以使用 handy regex method REGEXP_COUNT() , 自版本 11g 起可用:

select *
from mytable
order by regexp_count(
    'lorum ipsum testing with some dummy text lorum woop what testing again.',
    '(^|\W)' || testing || '(\W|$)'
) desc
fetch first 1 rows only

Demo on DB Fiddle :

ID | TESTING-: | :------ 2 | lorum  

Note: fetch first 1 rows only requires Oracle 12c (in earlier versions, you need a subquery and ROWNUM).

---

In Postgres, you can use regexp_matches() to generate an array of matches for each search word, and then count the elements:

select id, testing, count(m)
from 
    mytable t,
    regexp_matches(
        'lorum ipsum testing with some dummy text lorum woop what testing again.', 
        '\y' || t.testing || '\y',
        'g'
    ) m
group by id, testing
order by count(m)::int desc
limit 1

Demo on DB Fiddle