Preg Replace MySql Query

[askeli]

Hi can anyone help, is there such a function to do a preg replace on the results of a mysql query?

I curently use

SELECT * FROM table WHERE REPLACE(name,' ','-')='$name'

but would like to also replace periods commas undersores etc with out lots of REPLACE(REPLACE(REPLACE.

Many Thanks

[eelixduppy]

I'm assuming that you are running this query through PHP considering you have a PHP variable in it?

If so, here's a solution using preg_replace:

$pattern = "/[^a-z]/i";
$name = preg_replace($pattern,'',$string);
$query = "SELECT * FROM table WHERE name='$name'";

I hope this is what you are looking for. Good luck ;)

[askeli]

Actually its the result i need replaced not the search string i believe.

example $name = "blue-green" but in table name = "blue green" which obviously means thats fine, just replace "-" with " " but problem i come accross is table name may contain "blue-yellow green"

i may be going about this wrong

thanks

[eelixduppy]

I'm not exactly sure what you are trying to accomplish here to know if what you are doing is best, but it seems that you have a variable for the table name. If this is the case then there is probably a better way to go at this, but if you want to stick with this, then all you have to do is change the location of the variable:

$pattern = "/[^a-z]/i";
$name = preg_replace($pattern,' ',$string);
$name = rtrim($name); //strip whitespace off the end of string in case above preg_replace removed a character from the end of the string
$query = "SELECT * FROM table WHERE $name='$something'"; 

If you would like, explain a little more about what you are trying to do, and I'll see if I can help out any better ;)

[askeli]

I found out what i actually need is REGEX_REPLACE a mysql function which doesnt currently exist.

Thanks for your help ill have a go at your idea.

[herculese]

MySQL offers a REGEXP opereand although I am not sure about the REGEXP_REPLACE thing.

Instead of replacing the "bad" characters in the table field, you may try doing just the reverse. ie you can convert the $name variable into a regular expression and match it agains the field.

$name_regex = str_replace("-", "[^a-zA-Z0-9]", $name);
$name_regex = '^'.$name_regex.'$';

This will replace all instances of "-" in the $name variable with a regular expression pattern that will match with all possible bad characters that can come in the name field. So all the periods, commas etc in the name field will match to this when the created pattern is matched using REGEXP.

The query can be something like:

SELECT * FROM table WHERE `name` REGEXP '$name_regex'

Hope it helps.

[ My head keeps on saying there is something even simpler :? Anyone with another idea? ]