Hello,

We wrote a custom script to import our old site's database into Umbraco, but have noticed that now the new site has gone live (and content has been added/updated) that there was an error in the import.

The error is in the "Related Links" data type import and has placed an extra square bracket between the entries.

Entries in the database therefore look like:

<links><![CDATA[[
  {
    "caption": "Some caption", "link": 1163, "newWindow": false, "internal": 1163, "edit": false, "isInternal": true, "internalName": "Caption", "type": "internal", "title": "Link Title" 
  }, 
    [  <-- extra bracket!
  { 
   "caption": "Some caption", "link": 1163, "newWindow": false, "internal": 1163, "edit": false, "isInternal": true, "internalName": "Caption", "type": "internal", "title": "Link Title" 
  }
]]]></links>

This therefore generates a compilation error on the 1,000's of pages (out of 10,000+ pages) which are affected.

What's the best approach to correct this error? Rewriting the script and re-importing the data isn't really an option since new content has been added and old content has been updated.

A find/replace directly in the database seems to be an option, but the links appear in multiple places in the database (cmsPropertyData, cmsContentXml and cmsPreviewXml that I know of).

A further complication is that there seems to be a random number of spaces between the curly and square brackets eg }, [ {"caption or }, [ {"caption, making a simple find/replace more difficult and my understanding is that SQL doesn't support regex.

I can find affected entries using (or variant of):

 WHERE dataNtext LIKE '%}\,% %\[% %{% %"caption%' ESCAPE '\' OR dataNtext LIKE '%}\,\[{"caption%' ESCAPE '\')

Help!