3

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.

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
vintana
  • 2,893
  • 2
  • 18
  • 12

3 Answers3

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.

Alistair Knock
  • 1,806
  • 2
  • 16
  • 25
  • 1
    Hmm.. This can work but how about for phrase searches? This is more suitable for exact term searches. – vintana Mar 26 '09 at 13:28
0

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.

MikeW
  • 5,702
  • 1
  • 35
  • 43
  • Copying the text column to another, minus the apostrophe, is the last resort I have in mind. It doesn't seem right though. Can Lucene.NET or other ways to search workaround this issue? – vintana Mar 26 '09 at 13:25
0

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.

Alistair Knock
  • 1,806
  • 2
  • 16
  • 25