CODEWITHSUNDEEP
databasepostgresql
Pravin kumar
Pravin kumar

Reputation: 62

How to get the count of the given word in a column? using postgresql

I am new to PostgreSql, I am having a table like below,

enter image description here

I need to get output as count of the given word (test) in text_col like below table using ts_vector function, Please give me some suggestion, Thanks !.

enter image description here

Upvotes: 1

Views: 584

Answers (3)

Pepe N O
Pepe N O

Reputation: 2354

Using tsvector is like (globally)

select * from ts_stat('select to_tsvector(text_col) from document_table')
where word='test';

where ndoc is number of rows (documents) and nentry number of occurrences of the word

Or by row

select file_name, text_col, to_tsvector(text_col), 
substring(to_tsvector(text_col)::text, '''test'':(\d+(,\d+)*)') positions_in_text,
length(regexp_replace(substring(to_tsvector(text_col)::text, '''test'':(\d+(,\d+)*)'), '[^,]', '', 'g'))+1 occurrences 
from document_table
where text_col @@ to_tsquery('test');

Upvotes: 1

Saya
Saya

Reputation: 353

SELECT filename, cardinality(string_to_array(text_col,'test'))-1 
FROM documents;

dbfiddle

Upvotes: 2

Ftisiot
Ftisiot

Reputation: 1868

What about counting spaces?

The following takes the length of the original string, calculates the length of the string removing the spaces and then sums 1

with first_sel as (
    select 'Ciao io sono Ugo' mytext union all
    select 'Ciao io non sono Ugo' 
)
select mytext, length(mytext) - length(replace(mytext, ' ','')) + 1 nr_words from first_sel;

result

        mytext        | nr_words
----------------------+----------
 Ciao io sono Ugo     |        4
 Ciao io non sono Ugo |        5
(2 rows)

If it's not only about spaces you can try removing the needed characters with regex_replace

Upvotes: 0

Related Questions