How to find content by space for a specific string in Confluence
Purpose
To find a specific text string within the Confluence content and spaces, for example a URL, please refer to the following queries:
Solution
Query to find only pages with a specific string of text:
SELECT SP.SPACENAME, SP.SPACEKEY, C.TITLE, C.CONTENTID, C.CONTENTTYPE, C.CONTENT_STATUS, C.PREVVER
FROM BODYCONTENT AS BC
JOIN CONTENT AS C ON BC.CONTENTID = C.CONTENTID
JOIN SPACES AS SP ON C.SPACEID = SP.SPACEID
WHERE
C.CONTENTTYPE = 'PAGE' AND
BC.BODY LIKE '%<string-to-search-for>%'
ORDER BY SP.SPACENAME, C.TITLE;Query to find both pages and blogposts with a specific string of text:
SELECT SP.SPACENAME, SP.SPACEKEY, C.TITLE, C.CONTENTID, C.CONTENTTYPE, C.CONTENT_STATUS, C.PREVVER
FROM BODYCONTENT AS BC
JOIN CONTENT AS C ON BC.CONTENTID = C.CONTENTID
JOIN SPACES AS SP ON C.SPACEID = SP.SPACEID
WHERE
BC.BODY LIKE '%<string-to-search-for>%'
ORDER BY SP.SPACENAME, C.TITLE;- Query to find drafts with a specific string of text:
SELECT SP.SPACENAME, SP.SPACEKEY, C.TITLE, C.CONTENTID, C.CONTENTTYPE, C.CONTENT_STATUS, C.PREVVER
FROM BODYCONTENT AS BC
JOIN CONTENT AS C ON BC.CONTENTID = C.CONTENTID
JOIN SPACES SP ON SP.SPACEKEY = C.DRAFTSPACEKEY
WHERE
C.CONTENTTYPE = 'DRAFT' AND
BC.BODY LIKE '%<string-to-search-for>%'
ORDER BY SP.SPACENAME, C.TITLE;- Query to find comments with a specific string of text:
SELECT C.CONTENTID, C.CONTENTTYPE, C.CONTENT_STATUS, C.PAGEID
FROM BODYCONTENT AS BC
JOIN CONTENT AS C ON BC.CONTENTID = C.CONTENTID
WHERE
BC.BODY LIKE '%<string-to-search-for>%' AND
C.PREVVER ISNULL AND
C.CONTENTTYPE = 'COMMENT'
ORDER BY CONTENTID- Query to find the page's that contain the comment records extracted from the previous query above:
SELECT SP.SPACENAME, C.CONTENTID, C.TITLE, C.CONTENTTYPE, C.CONTENT_STATUS
FROM CONTENT C
JOIN SPACES AS SP ON C.SPACEID = SP.SPACEID
WHERE C.CONTENTID
IN (SELECT C.PAGEID
FROM BODYCONTENT AS BC
JOIN CONTENT AS C ON BC.CONTENTID = C.CONTENTID
WHERE
BC.BODY LIKE '%<string-to-search-for>%' AND
C.PREVVER ISNULL AND
C.CONTENTTYPE = 'COMMENT')
ORDER BY SP.SPACENAME, C.TITLE;
Please note that these queries may need to be adjusted depending on the database in use.
Last modified on Mar 21, 2024
Powered by Confluence and Scroll Viewport.