Welcome to WebmasterWorld Guest from 3.80.6.254

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Preg Replace MySql Query

     
10:25 pm on Oct 13, 2006 (gmt 0)

Junior Member

10+ Year Member

joined:Nov 29, 2003
posts:94
votes: 0


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

10:53 pm on Oct 13, 2006 (gmt 0)

Senior Member

joined:Nov 12, 2005
posts:5967
votes: 0


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 ;)

11:33 pm on Oct 13, 2006 (gmt 0)

Junior Member

10+ Year Member

joined:Nov 29, 2003
posts: 94
votes: 0


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

1:44 am on Oct 14, 2006 (gmt 0)

Senior Member

joined:Nov 12, 2005
posts:5967
votes: 0


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 ;)

2:09 pm on Oct 14, 2006 (gmt 0)

Junior Member

10+ Year Member

joined:Nov 29, 2003
posts:94
votes: 0


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.

6:34 pm on Oct 14, 2006 (gmt 0)

New User

10+ Year Member

joined:May 18, 2006
posts:9
votes: 0


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? ]