I would like to know how to exclude apostrophes from indexing in full-text search.
For example, if someone enters the search term for “o'brien” or “obrien”, I would like it to match all cases where someone called a match “O'Brien” or “OBrien”.
However, if I search:
select * from MyTable where contains (fullName, '"o''Brien*"')
It returns only those that have an apostrophe. But if I do this:
select * from MyTable where contains (fullName, '"oBrien*"')
It returns only those that do not have an apostrophe.
In short, I want to know if it is possible for the FTS to index “O'Brien” and “OBrien” as “obrien” so that I can find both.
While the solution is:
select * from MyTable where contains (fullName, '"oBrien*" OR "o''Brien*"')
will work, however I cannot make this assumption if the user entered "obrien".
, SQL Server 2005, 2008 .