SQL Query for Last Names with Apostrophes

I have a database of last names, first names, addresses, etc.

I am trying 开发者_StackOverflow中文版to search by last name, and I have no problem with escapting apostrophes, for example, the data in the table is "O'Malley" 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 "O'Malley" record from the table. Is there any way to ignore the apostrophe in the table?

Or, is my only other option to create a last name field that is stripped of the apostrophe, and then use and OR statement to check both.

Try something like:

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

EDIT - Corrected REPLACE syntax

You could probably check against the REPLACE('''','',column_name) value and save yourself an additional "indexable" column.

If your database supports SOUNDEX - you could searh on names that "sound" like omalley. Soundex is really helpful when you want to try and find people quick and not have to ask how to spell something...More about Soundex

For example the name I have seen Thomas spelled Tomas, Tomes, Tomus - even though all spelled differntly, soundex will find it - cause they all "sound the same". Especially nowadays when people are looking for different ways to spell common names, this can be very helpful.

Replace all instances of ' with '? and use REGEXP.

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

If you're not familiar with ? in regex, it means "this character can appear here, but it doesn't have to." For example, colou?r would match both "color" and "colour".

Note: This solution works in MySQL but I don't know what DBMS you're using.