I have a list of names, etc in a mySQL database where some contain periods as well as commas, /, &, etc.
How can I include those names in the results if a user does not enter those extra characters in the search form ?
Basically I want those characters completely ignored.
A record might be for Mr Black and I want the results to display that record if a user searched for Mr Black or Mr. Black
Alternatively, The record could include the period (as in Mr.) so it should work inversely too.
I tried using LIKE in my sql query but this didn't provide the results I need.
To make this even more interesting, there are also situations where & and AND need to be interchanged. So a search for 'Black and Son' should result in records that are actually 'Black & Son', and inversely.
What would be the best approach for this?
I am not able to modify the way names are formatted within the records, but I COULD add an extra field where alternate name formats are stored. I would really like to avoid this option as it would mean manual modification to such records.
Thanks in advance for your help!