• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 890
  • Last Modified:

mySQL: Multiple Replace

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

0
hankknight
Asked:
hankknight
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.-..
0
 
K VDatabase ConsultantCommented:
preg_replace( pattern, replacement, subject ,[limit])

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

Ref: http://www.webcheatsheet.com/php/regular_expressions.php#replace
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now