Exclude apostrophes from SQL Server Full Text Search

Question

Hi,

I would like to know how to exclude apostrophes from being indexed in full text search.

For example, if someone enters a search term for "o'brien" or for "obrien" I would want it to match all cases where someone's name matches either "O'Brien" or "OBrien".

However, if I search on:

select * from MyTable where contains (fullName, '"o''Brien*"')

It returns only ones with an apostrophe. But if I do:

select * from MyTable where contains (fullName, '"oBrien*"')

It only returns the ones without an apostrophe.

In short, I want to know if it is possible for FTS to index both "O'Brien" and "OBrien" as "obrien" so that I can find both.

While the solution:

select * from MyTable where contains (fullName, '"oBrien*" OR "o''Brien*"')

would work, however, I can't make that assumption if the user entered "obrien".

I'm looking for a solution that works both on SQL Server 2005 and 2008.

Answer 1

depending on how much space you have, you could add another column

fullName_noPunctuation

containing only the alpha characters of the name, strip punctuation from your search criteria, and search the punctuation-free column instead.