Search for records with apostrophe
I am trying to search the database for a keyword like Obrien and the results should return all the records with O'Brien too. I have tried with fulltext search of MySql. The problem with it is the resultset can cntainn only 1 record but fulltext feature requires the resultset to contain atlest 3 records or 50% of the matched results.
Does any开发者_开发技巧body know how to do this?
could you do something like:
REPLACE(name,'''','')
I tested it:
DECLARE @name VARCHAR(10)
SELECT @name = 'O''brien'
SELECT @name as "pre", REPLACE(@name, '''', '') as "post"
and it game me O'brien for the pre column and Obrien for the post column.
edit after response from OP:
try this
SELECT TOP 1 *
FROM table
WHERE REPLACE(name, '''', '') = REPLACE(searchString, '''', '')
Thank you so much for your response. Truly appreciate your help. I tried this and it worked for me. I got this solution from this site itself.
Title : Apostrophes and SQL Server FT search
the last solution as suggested by "Alistair Knock" which is
SELECT surname FROM table WHERE surname LIKE '%value%' OR REPLACE(surname,'''','') LIKE '%value%'
This works even when FT is not enabled.
Thanks you everyone. Hope this helps somebody.
精彩评论