MySQL – How to Return Results That Contain or Do Not Contain Punctuation

likeMySQLstring-searching

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%";

or

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%");

or

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:

EDIT

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%'

EDIT x2

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.

Best Answer


I had a similar situation a while ago. It was a project in Ireland for a membership system for a cultural organisation and one of the requirements was that the system handle Irish names.

Irish names can be very complex with O, O', Ó, Ó', Mac, Mc', Mc (all with or without spaces after) and in some cases names like Ua hÚigínn (not a typo - the small h followed by upper case Ú is correct in Irish). As you can see, there are also accented characters (a, e, i, o and u can take acute accents). The problem is that many people use many different variants of these names with/without apostrophes, accents and alternative spellings.

You can look on these like your differing spellings in your some_value field!

What we did was to create a "shadow" (some_value_search) column and search on that! On INSERT or UPDATE, we eliminated apostrophes, accents, hyphens and spaces (in the names) using a TRIGGER. Obviously, your phrases need spaces, but you could turn person's thing into PERSONS THING and thing-thing into THINGTHING.

Then you display your data using the original some_value field, but always search on some_value_search.

This was in the days before computed/calculated/generated fields. But MySQL (as of 5.7.6) has them and you should be able to eliminate a lot/most/all of any trigger code using them.

col_name data_type [GENERATED ALWAYS] AS (expression)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

Your (expression) above could be (REPLACE(REPLACE(field, '-', ''), ''', '') to eliminate spaces and hyphens. However, thanks to MySQL's lack of a [TRANSLATE()][3] function, the code for making a string all-capitals is horrible (26 nested REPLACEs!). If you need to do anything complex, there are always TRIGGERs, but the above solution is fairly standard nowadays!

Related Question