Q:

Removing single quotes from comparison in select statement

I have a table where a field can have single quotes, but I need to be able to search by that field without single quotes. For example, if the search query is "Johns favorite", I need to be able to find a row where that field contains "John's favorite". I was looking into regex for it, but that seems to return a 0 or 1 when used in a select statement, if I'm understanding it correctly.

A:

Try using REPLACE:
SELECT 
   IF( 
       REPLACE("John's favorite","'","") = "Johns favorite" , 
       "found",
       "not found"
   )

It's not optimal but it should do the job.

