I'm having trouble using matches with spatial querys. I have a number of similar SQLs that update the notes field where I am querying against polygons within a certain distance. There are instances where some polygons have the same name and each is returned where I would like to only include 1 result for each unique name. I have come up with the following query using the 'matches' operator which works for everything except where the result contains brackets and it is then returning all of those results again.

SET notes = IIF(notes matches '.*Interest: ' + spatial.SSSI_NAME + '.*',notes,notes +'&CHAR(10)&"Sites of Special Scientific Interest: ' +spatial.SSSI_NAME + ';"');

I have looked into using [ ] to escape the brackets and for a set string (ie 'test(test)') I can use matches '.*test[(]test[)].*' and it works but as the brackets will be in various places I'd need to escape the entire result and I'm not sure how to do this.

I've also looked at storing the result as a variable and changing the brackets to { } using SUBST which does work but with my limited SQL knowledge I can only get it to return one result per query.

Any thoughts or suggestion on the easiest way to achieve this?