Find and Replace on KB Articles?

Russell_KlvacekRussell_Klvacek Customer IT Monkey ✭
Many of our knowledge articles contain a URL that is now out of date and needs to be updated.
Is there any supported way to do a find and replace operation on the URLs that need updating across all KB articles? Through the database via SQL script or otherwise.

Answers

  • Steve_WrightSteve_Wright Cireson Support Advanced IT Monkey ✭✭✭
    Hi Russell,
    Thank you for your inquiry. As the content is stored as a binary object in SQL, there is no way to find/replace from the KnowledgeArticle table. You can search/replace from the viewer or html view for each KB article.

    Hope that helps!

    Thanks,
  • Rick_HancksRick_Hancks Customer Adept IT Monkey ✭✭
    But if you have thousands of articles that can be a little challenging.  A find/replace across all articles is a fairly common tool available in many knowledge tools.  I would also like to see a utility that would parse through all articles in the knowledge base and validate url's that exist in articles.
  • Tom_HendricksTom_Hendricks Customer Super IT Monkey ✭✭✭✭✭
    edited December 2016
    Setting aside matters such as performance for a moment (to return to later I'm sure...), couldn't we just convert the binary to text inside of the update query, perform the replace, then set the column to the new text, converted back to binary?

    When I imported our KB articles from another system, I had to convert them to binary, and I plan on performing the same operation as @Rick_Hancks on some outdated URL's.  I plan to test what I am describing for our own needs and then share the results, but for now, here is the approach I am planning... (pardon the lack of indents)

    --The replacement, using analyst content as the example (obviously replace the bracketed values...):
    SELECT REPLACE(CONVERT(nvarchar(max), AnalystContent, 2), {BAD_STRING}, {GOOD_STRING}) FROM dbo.KnowledgeArticle

    --Put it together with an update
    UPDATE dbo.KnowledgeArticle
    SET AnalystContent
    SELECT CONVERT(varbinary(max), REPLACE(CONVERT(nvarchar(max), AnalystContent, 2), {BAD_STRING}, {GOOD_STRING}), 'nvarchar(max)') FROM dbo.KnowledgeArticle
    I have not tested this yet, and my SQL is a bit rusty, so it may be necessary to include the id column in a WHERE clause and/or join an A and a B version of the table to make sure the rows match between update and select....or not.  :)  Someone keep me honest here.
  • Rick_HancksRick_Hancks Customer Adept IT Monkey ✭✭
    We did build a Power Shell utility that allowed us to find/replace url's that were in our articles.  Our document repository moved to a new SharePoint site and we needed to update all of our url's that linked to these documents.  It also does text replacement too.
  • Justin_WorkmanJustin_Workman Cireson Support Ninja IT Monkey ✭✭✭✭
    @Rick_Hancks
    Thanks for the follow up on this thread!  I'm curious if your utility used the API or some other method of doing the replace.  Would it be possible for you to share the tool?
  • Tom_HendricksTom_Hendricks Customer Super IT Monkey ✭✭✭✭✭
    Quick comment, since this popped up in my notifications today and since I never returned to my comment above. 

    We ended up doing the approach I described above, in order to import KB articles from the old system.  It worked well enough as a one-time procedure to replace certain URLs in the article text.  However, it would have been more powerful with regular expressions and this gets messy in T-SQL (although it is possible).

    For an ongoing process, if you want to add a UI, or to more easily utilize regular expressions, I recommend @Rick_Hancks' approach.
  • Rick_HancksRick_Hancks Customer Adept IT Monkey ✭✭
    I did post the tool on this Community Upload site.
  • Adam_DzyackyAdam_Dzyacky Customer Contributor Monkey ✭✭✭✭✭
    Did something similar for a mass import only with PowerShell to do the HTML to Hex conversion and then write rows directly into the DB.
Sign In or Register to comment.