mySQL: Multiple Replace

Posted on 2009-04-22
Last Modified: 2013-12-12
I use the following PHP / mySQL code to search for matches after removing spaces.  Now I want to remove multiple characters, not just spaces:


I can do this in PHP like this:
      str_replace(array('!', '+', '$', '*', '-', '.', '?', ',', \''', ';', '_', '(', ')' )','',$term)

But how can I do this with mySQL?
$query = "select `name` from `organizations` where replace(`name`, ' ', '') LIKE '%". str_replace(' ','',$term) . "%'";

Open in new window

Question by:hankknight
    LVL 142

    Accepted Solution

    you will have to apply replace for each character to be replaced, like this:
    $query = "select `name` from `organizations` where replace(replace(`name`, ' ', ''),'!', '') LIKE '%". str_replace(' ','',$term) . "%'";

    Open in new window

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    you might consider to create a second column clean_name in your table, and have the "cleaned" value there, so you don't need to do this all the time, and you could even profit from an index on the field.-..
    LVL 14

    Assisted Solution

    LVL 21

    Expert Comment

    preg_replace( pattern, replacement, subject ,[limit])

    preg_replace("/!+$*-.?,';_()/", '',STRING)


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
    Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

    728 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