Search for records with apostrophe

Question

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 anybody know how to do this?

Answer 1

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, '''', '')

Answer 2

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.