I Need to ignore non-alpha characters in PHP / mySQL search...

Posted on 2004-11-15
Medium Priority
Last Modified: 2013-12-12
Hello All,

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!

Question by:shmanazle

Accepted Solution

jkna_gunn earned 672 total points
ID: 12590821
i would use str_replace function to replace any characters you dont want. only prob is that you have to pull back the records first and then remove all the unwanted chars then compare with the search query entered.


$name = str_replace('&','and',$db_name); // where db_name is field from row of db record

you can do the same if you want to remove , / . etc just replace them with either a space or nothing

e.g. $field = str_repalce('.','',$db_field);

mysql does have a replace function that is similar to php one so try it out
e.g. "select replace({your_field_name},'&','and') where....."


Assisted Solution

_GeG_ earned 664 total points
ID: 12591206
I have only a real ugly and cpu wasting solution, so just use it if it is not you server :>

for ($i=0; $i<strlen($search); $i++){
$query="SELECT * FROM table WHERE field REGEXP '$s'";
LVL 10

Assisted Solution

frugle earned 664 total points
ID: 12610221
Fulltext searching does this automatically.



Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses
Course of the Month17 days, 1 hour left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question