/
How to change URLs embedded in a HTML macro through database

How to change URLs embedded in a HTML macro through database

This article explains step by step instructions on identifying and changing the URLs embedded in Confluence through database.

Instructions

  • Create a database backup before changing anything.
  • Additionally, any database modifications must be done while Confluence is shut down and not running.

Search for URLs in the Confluence database:

  1. Stop Confluence following your standard procedure.
  2. Run a SQL statement to update the body content. An example is shown below:

    SQL query to identify the embedded URL's
    select c.CONTENTTYPE,c.TITLE, s.SPACENAME, s.SPACEKEY, s.SPACETYPE, s.SPACESTATUS 
    from content c join spaces s on c.SPACEID=s.SPACEID 
    where CONTENTID in 
    (select CONTENTID from bodycontent where BODY like '%<INSERT_KEYWORD_HERE>%')
  3. Replace the <INSERT_KEYWORD_HERE> with your keyword. The '% ' symbol represents a wildcard search.

  4. The SQL results return the CONTENTTYPE,TITLE with other space details including the SPACESTATUS (either CURRENT or ARCHIVED).

    Sample query :

    Example
    select c.CONTENTTYPE,c.TITLE, s.SPACENAME, s.SPACEKEY, s.SPACETYPE, s.SPACESTATUS from CONTENT c join SPACES s on c.SPACEID=s.SPACEID  where CONTENTID in (select CONTENTID from BODYCONTENT where BODY like '%https://www.w3schools.com/html/html_examples.asp%')

    Output: