Apostrophes and SQL Server FT search

Question

I have setup FT search in SQL Server 2005 but I cant seem to find a way to match "Lias" keyword to a record with "Lia's". What I basically want is to allow people to search without the apostrophe.

I have been on and off this problem for quite some time now so any help will really be a blessing.

Answer 1

EDIT 2: just realised this doesn't actually resolve your problem, please ignore and see other answer! The code below will return results for a case when a user has inserted an apostrophe which shouldn't be there, such as "abandoned it's cargo".

I don't have FT installed locally and have not tested this - you can use the syntax of CONTAINS to test for both the original occurrence and one with the apostrophe stripped, i.e.:

SELECT *
FROM table
WHERE CONTAINS ('value' OR Replace('value', '''',''))

EDIT: You can search for phrases using double quotes, e.g.

SELECT *
FROM table
WHERE CONTAINS ("this phrase" OR Replace("this phrase", '''',''))

See MSDN documentation for CONTAINS. This actually indicates the punctuation is ignored anyway, but again I haven't tested; it may be worth just trying CONTAINS('value') on its own.

Answer 2

I haven't used FT, but in doing queries on varchar columns, and looking for surnames such as O'Reilly, I've used:

surname like Replace( @search, '''', '') + '%' or
Replace( surname,'''','') like @search + '%'

This allows for the apostrophe to be in either the database value or the search term. It's also obviously going to perform like a dog with a large table.

The alternative (also not a good one probably) would be to save a 2nd copy of the data, stripped of non-alpha characters, and search (also?) against that copy. So there original would contain Lia's and the 2nd copy Lias. Doubling the amount of storage, etc.

Answer 3

Another attempt:

SELECT surname
FROM table
WHERE surname LIKE '%value%'
OR REPLACE(surname,'''','') LIKE '%value%'

This works for me (without FT enabled), i.e. I get the same results when searching for O'Connor or OConnor.