PL / SQL word count from string

Question

can you please help me how to count words in string? It is format like:

n_var VARCHAR2(1000) := 'Hello, I like ham pizza more than mozzarella pizza.'

I need output something like this: hello => 1, I => 1, like => 1, ham => 1, pizza => 2...

My idea is to use associative array but I am not sure how to do it. Thank you.

I don't know how to split it by spaces and add it into associative array in PL/SQL — Tatarka, Feb 05 '19 at 17:06

score 0 · Answer 1 · answered Feb 05 '19 at 17:25

Here is one solution. It deploys regular expressions to split the words from your string into tokens. These tokens are then counted with a simple aggregation function. So, pure SQL and no need for associative arrays or any other PL/SQL collection.

with dat as (
    select 'Hello, I like ham pizza more than mozzarella pizza.' as str from dual
)  , tkns as (
    select regexp_substr(str, '([[:alnum:]]+)', 1, level) as tkn
    from dat
    connect by level <= regexp_count(str, '[\., ]')
)
select tkn, count(*)
from tkns
where tkn is not null
group by tkn
order by tkn
/

Undoubtedly there are more elegant regex solutions, but at least this one works.

score 0 · Accepted Answer · answered Feb 05 '19 at 18:08

One another option would be the query below :

select word, count(1) as repeating
  from
  (
    with t(str) as
    (
     select 'Hello, I like ham pizza more than mozzarella pizza' from dual   
    )    
    select regexp_replace(regexp_substr(str,  '[^\ ]+', 1, level),'[^a-zA-Z]','')
        as word
      from t
     cross join dual
    connect by level <= regexp_count(str,  '[^\ ]+') 
   )   
 group by word
 order by repeating desc, word;

WORD       REPEATING
---------- ---------
pizza          2
ham            1
Hello          1
I              1
like           1
more           1
mozzarella     1
than           1

PL / SQL word count from string

2 Answers2