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.
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
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.
