Search for the most occurring value in a string with SQL

user_7791653 0
Viewed 96

So if you have a string like:

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

and you have a database table like:

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

How do I check what value is the most occurring in the string using sql only.

So in this case it would return: 2 | lorum.

At first I thought the LOCATE() function would be useful. I tried for example:

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

Is this possible? If not what would be the best approach?

count sql-order-by sql string
4 Answers

In Oracle, you could use handy regex method REGEXP_COUNT(), available since version 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

user_106767160

This is painful, but you can use a like and some string manipulations to count the number of matches within the string:

select t.testing,
       (length(v.str) - length(replace(v.str, t.testing, ''))) / length(t.testing) as num_times 
from (values ('lorum ipsum testing with some dummy text lorum woop what testing again.')
     ) v(str) join
     t
     on str like concat('%', t.testing, '%')
order by num_times desc
limit 1;

Note: This is generic. In Postgres, I would split the string into words and do equality comparisons.

user_11440350

With Postgres this is quite easy:

select word, count(*)
from regexp_split_to_table('lorum ipsum testing with some dummy text lorum woop what testing again.', '\s') as x(word)
group by word
order by count(*) desc
user_3303150

Here is an approach (implemented in Ms-SQL) utilizing STRING_SPLIT(<string>,<separator>) to turn test string into a table. That table can be joined with the dictionary, and all matches counted using GROUP BY with a Count. I called dictionary table DICTIONARY

SELECT sub.testing, COUNT(sub.testing) FROM 
(   SELECT d.testing FROM DICTIONARY d
    INNER JOIN ( SELECT value FROM STRING_SPLIT('lorum ipsum testing with some dummy text lorum woop what testing again.', ' ') ) s
    ON d.testing = s.value
)   sub
GROUP BY sub.testing
ORDER BY COUNT(sub.testing) DESC

Results: all matched values from the DICTIONARY table will be listed with the counts, in descending order

user_2234890
Related