How to count the number of times a word appears in Confluence
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
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
leninstead oflength1 2 3 4 5select 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>'
Was this helpful?