Is there a way within MySQL to return results using a
LIKE statement that contain or do not contain punctuation characters such as apostrophes and hyphens?
For example if I have one of the following queries:
SELECT * FROM some_table WHERE some_value LIKE "%this-thing%";
SELECT * FROM some_table WHERE some_value LIKE "%person's thing%"
Is there a way in which I could obtain the same results as the queries below but with a single LIKE clause?
SELECT * FROM some_table WHERE (some_value LIKE "%this-thing%") OR (some_value LIKE "%this thing%");
SELECT * FROM some_table WHERE (some_value LIKE "%person's thing%") OR (some_value LIKE "%persons thing%");
Basically a function that when searching would return a result if either version of the text exists ("person's thing" or "persons thing"), something like:
Is there a function within mysql which would return only alpha numeric characters within a column? For example if the column value where "something's & other-stuff" then it would be returned as "somethingsotherstuff"? So a query would look something like:
SELECT * FROM table WHERE magic_function(column_name) LIKE '%words%'
For the time being I have resolved this by chaining together REPLACE() and replacing all special characters in the column value with "" before doing the comparision. Value being compared has also been stripped of all special characters.
REGEXP_REPLACEthat does it easily. Otherwise, I recommend using client code.