SQL query for last names with apostrophes

Question

I have a database with names, names, addresses, etc.

I'm trying to search by last name and I have no problem escaping apostrophes, for example, the data in the O'Malley table, and if I search for O'Malley, I get the intended results,

However, I would also like to be able to search for “omalley” and still return the record “O'Malley” from the table. Is there a way to ignore an apostrophe in a table?

Or, my only other way is to create a last name field that is devoid of an apostrophe, and then use the OR statement to test both.

Answer 1

, REPLACE('''','',column_name) .

Answer 2

- :

SELECT * -- replace with appropriate field list
FROM MyTable
WHERE LOWER(REPLACE(column_name,'''','')) = 'omalley'

EDIT - REPLACE

Answer 3

SOUNDEX - , "" omalley. Soundex , , - ... Soundex

, , , , , - , soundex , " ". , , .

Answer 4

' '? REGEXP.

SELECT *
  FROM person
 WHERE name REGEXP 'o\'?malley'

If you are not familiar with ?the regular expression, this means that this character may appear here, but this is not necessary. "For example, colou?rboth" color "and" color "will match.

Note. This solution works in MySQL, but I don’t know which DBMS you are using.