目的

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

ソリューション

• 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>'