Find and Replace on KB Articles?

Russell_Klvacek

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.

Steve_Wright

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_Hancks

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_Hendricks

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)

SELECT CONVERT(varbinary(max), REPLACE(CONVERT(nvarchar(max), AnalystContent, 2), {BAD_STRING}, {GOOD_STRING}), 'nvarchar(max)') FROM dbo.KnowledgeArticle
--The replacement, using analyst content as the example (obviously replace the bracketed values...):<br>SELECT REPLACE(CONVERT(nvarchar(max), AnalystContent, 2), {BAD_STRING}, {GOOD_STRING}) FROM dbo.KnowledgeArticle<br><br>--Put it together with an update<br>UPDATE dbo.KnowledgeArticle<br>SET AnalystContent<br>

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_Hancks

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_Workman

@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_Hendricks

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_Hancks

I did post the tool on this Community Upload site.

Adam_Dzyacky

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.