通过mysqli搜索忽略非字母数字字符

Question

Having a bit of an issue trying to figure out the correct way to search for something in a mysql database (using php) with a user inputted search string... but I have to use LIKE and % wildcards and also compare the user search to the database field ignoring any non-alphanumeric characters. It's pretty complex, and while I've seen some other questions on this subject here on StackOverflow, none seem to have an answer that works.

So for example, let's say I have a database with an 'Aviation' table with the following:

MAKE               NAME1          NAME2
--------------------------------------------------------
Boeing             B-17G          Flying Fortress
Boeing             F-18E          Super Hornet
Lockheed           F-16C          Viper
Supermarine        Spitfire       Mk IX
McDonnellDouglas   F-18C          Hornet

And now I have a user that searches for 'f18'...

1) Since I won't know if this is the make, name1, or name2 of the airplane plus I have no idea if the user entered the exact type (ie f-18c/f18c vs just f18) I need to have the user inputted search able to, using LIKE wildcards, search all three fields for %f18%. This is an easy WHERE Make LIKE '%f18%' OR Name1 LIKE '%f18%' OR Name2 LIKE '%f18%'

2) While that is simple, the problem is that in the database the f18 is listed as F-18... so even %f18% will find NO matching records. I've tried indexing the Make, Name1, and Name2 fields as a FULLTEXT index (which I had read would ignore alphanumeric characters), but I still seem to get no returned records when I use LIKE %f18% searching which should return both the F-18C Hornet and the F-18E Super Hornet matches. Is it possible to use RegEx to solve this? I can easily strip out the non alphanumeric characters from the user inputted search in case they do search for f-18 instead of f18, but I have to keep them in the database.

So the question is how can I use mysqli to search multiple fields using LIKE comparisons and ignoring non alphanumeric characters from a user inputted search? Is this even possible?

Thanks!

Answer 1

Hi if you are sure that you not add alphanumeric in your search you can use this query this will give you desired result

SELECT  * from Aviation where replace(NAME1,'-','')  like '%f18%'  

once try to run it in phpmyadmin.

for more info about replace(). Please read this http://www.w3resource.com/mysql/string-functions/mysql-replace-function.php