How to replace part of a URL in remotelink table in JIRA sql db

Apologies in advance if this is the wrong place for such a request, but I was adviced by atlassian support to reach out to the developer community, as they eventually found my request to be somewhat outside of what they support.

The case is that we have several thousand url links in one of our jira projects. And now they decided to change servers, so all the links need to point to the new server instead of the old. Also we should only replace the main part of the url, so they don’t loose the last part of the url, which are references to spesific bugs. It was suggested to do this change directly in sql, since manually editing and updating all these references in Jira will take ages.

We first got a suggestion from JIRA support to do something like this:
– update all the mentioned url’s
update jiraschema.remotelink set URL = ‘newurl’ where URL like ‘%oldurl%’;

But this resulted in the urls being replaced completely with only the new url, ignoring the last part which refers to case/bug id etc. (i.e. “oldurl//show_bug.cgi?id=10340” would be replaced with “newurl”, removing the “/show_bug.cgi?id=10340” part )

So we changed the replace query to:
–New update url’s statement
update jiraschema.remotelink set URL = replace(URL, ‘oldurl’,‘newurl’) where URL like ‘%oldurl%’

But now we get “Argument data type ntext is invalid for argument 1 of replace function.”
Seems the ntext data type is no longer supported in the sql version we are using.

I tried to google for alternatives to the replace function, but I can’t figure out exactly how to write a query that will find all references to the old url, and replce it with the new, leaving the last part of the url’s intact.

We are running Jira 7.10.2 and the db is running on sql server 2017 (14.0.3038)

Any suggestions will be highly appreciated.

Edit: Had to remove the actual url’s since I’m apparantly not permitted to post url’s as a new forum user