Ignoring Apostrophes In Contains

Question

Simple question, perhaps a difficult answer. We're using CONTAINS but when matching criteria containing apostrophes (because these are for French road names), then obviously they don't match. So for example, searching for "Lislet" when the database contains "L'islet" yields nothing.

I've looked into using dictionaries and thesauruses (SQL features) but unfortunately neither do the job. Thesauruses looked very promising, but upon testing in our environment, it became apparent that they don't support apostrophes, which is a big shame 'cause that would've worked perfectly.

The only other thing we're thinking of doing is creating a fully indexed view that strips out the apostrophes.

Any other bright ideas?

Answer 1

Have you examined the Full-Text Catalog for your database? There is an Accent sensitivity option. You can select Sensitive or Insensitive. I think you want this to be set to Insensitive, so that the apostrophes are ignored.