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

Posted on 2004-11-15
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
    LVL 6

    Accepted Solution

    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....."

    LVL 9

    Assisted Solution

    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

    Fulltext searching does this automatically.


    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Both Easy and Powerful How easy is PHP? (  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
    Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now