CODEWITHSUNDEEP
oracle-databaseoracle-bi
tripuz
tripuz

Reputation: 11

Word count for all words in a Oracle BI column

I'm a user of Oracle BI (v. 11.1.1.7.141014). I have a text column "description" and would like to create a new table with the word count for all words in that column. So for instance:

Source:

Description
___________
This is a test
Just a test

Result:

Word    Count
_____________
a       2
test    2
is      1
just    1
this    1

Would it be possible? I have a user account, (no administration features), but I can work on reports (tables, pivot tables, etc.), data structures, custom SQL queries (limited to reports and data structures) and so on...

Thanks in advance

Upvotes: 1

Views: 242

Answers (1)

user5683823
user5683823

Reputation:

Defining "word" as any sequence of one or more consecutive English letters (upper or lower case), and assuming that "this" and "This" are the same, here is one possible solution. The first line of the code ends in "... from a)," substitute your table name in place of "a" (for my own testing purposes, I created a table with your input data and I called it a).

with b (d, ct) as (select Description, regexp_count(Description, '[a-zA-Z]+') from a),
     h (pos)       as (select level from dual connect by level <= 100),
     prep (word)   as (select lower(regexp_substr(d, '[a-zA-Z]+', 1, pos)) from b, h where pos <= ct)
select   word, count(word) as word_count 
from     prep 
group by word
order by word_count desc, word
/

The solution needs to know beforehand the maximum number of words per input string; I used 100, that can be increased (in the definition of h in the second line of code).

Upvotes: 1

Related Questions