How to count the number of times a word appears in Confluence

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

Search in Confluence offers the ability to find pages that contain a word, but does not show how many times that word appears. 

Solution

  • Try the following query:
  • This has been tested in PostgreSQL, MySQL and Oracle Database. SQL Server uses the function len instead of length

    select sum (length(bc.BODY) - length(replace(bc.BODY,'<word-to-count>',''))) / length('<word-to-count>') as word_count 
    from BODYCONTENT bc    
    join CONTENT c on bc.CONTENTID = c.CONTENTID                                                                          
    join SPACES s on c.SPACEID = s.SPACEID                                                                                
    where c.PREVVER is null;
  • You can add additional qualifiers to be more granular

    • To restrict to a specific space: and s.SPACEKEY = '<space-key>'
    • To restrict to a specific page (that you know the id of): and c.CONTENTID = <the-page-id>
    • To restrict to a specific page (that you know the title of): and c.TITLE = '<the-title-of-the-page>'

Last modified on Feb 26, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.