sql 'smart' replace?

iblastoff · 2007-11-28 23:31:13

i’m currently working on a simple search & replace plugin using standard sql queries but i’m having an issue with the following:

firstly i’m pretty new to sql (specifically mysql) so forgive any blunders.

i need to do this in mysql and i’m having an issue trying to correctly replace occurrences of text within a body of text thats stored in the database.

currently i have something like this:

UPDATE table SET 'field' = replace('field', "old_text", "new_text")

and it somewhat works except it replaces ANY instance of the old text.
for example, a sentence that reads “the man went shopping in manhattan.”

i want to replace all occurrences of the word “man” with “cat”. if i use the above code, i get the following:

“the cat went shopping in cathattan”

i WANT to be able to get “the cat went shopping in manhattan”.

i’ve tried adding spaces around the “old_text” like so -> “ man “ and that DOES pick up the word ‘man’ by itself without affecting ‘manhattan’, but unfortunately that would mean it would not be able to pick up “Man, I am awesome” or “You’re awesome, Man”. since it doesn’t have a space preceding or proceeding it.

any help would be appreciated.

jm · 2007-11-29 02:32:34

I’m pretty sure you’ll need to use a regular expression. Replace doesn’t support REGEX, so you’ll need to:

Grab the row with PHP
Modify the string with regex (preg_replace)
Update table

Check out general PHP regex and word boundaries. Sorry I’m not much help, but I suck at regex (aside from \bsomeword\b.

Last edited by jm (2007-11-29 02:34:20)

iblastoff · 2007-11-29 08:38:51

hey jm. that stuff may be a bit beyond my skill lvl at this point (still learning wholeheartedly) but thank you for the links! time to do some reading.

Mary · 2007-11-29 16:05:01

I would use MySQL’s regex ability to grab only the rows you need to modify.

jm · 2007-11-30 08:05:38

Here’s a quick-and-dirty example. Maybe this is what you want?

Sample data

mysql> SELECT * FROM samples;
+----+-------------------------------------+
| id | sentence                            |
+----+-------------------------------------+
|  1 | The man went shopping in Manhattan. | 
|  2 | Man, I am awesome                   | 
+----+-------------------------------------+
2 rows in set (0.00 sec)

PHP

<?php
	$id = 1;
	$tableName = 'samples';
	mysql_connect('localhost', 'root', '') or die(mysql_error());
	mysql_select_db('dev_replace') or die(mysql_error());

	$sentence = mysql_fetch_row(mysql_query("SELECT sentence FROM $tableName WHERE id = $id;"));

	$regex = '/\bman\b/';
	/*case-insensitive:
	$regex = '/\bman\b/i';
	*/
	$replacement = 'cat';
	$replaced = preg_replace($regex, $replacement, $sentence[0]);
	echo $replaced;

	//---Update db
	mysql_query("UPDATE $tableName SET sentence = '$replaced' WHERE id = $id");
?>

Hope that helps!

iblastoff · 2007-11-30 13:15:05

hey jm thanks for the sample code. definitely helped my understanding a bit more.

i was just wondering if anyone knew this:
in the textpattern table i can see the fields:
body, body_html ,excerpt, excerpt_html, title, title_html.

in a populated site with articles i’ve noticed that title_html doesn’t ever seem to hold anything. when would that field ever get populated?

Last edited by iblastoff (2007-11-30 13:15:24)

iblastoff · 2007-11-30 13:53:59

Mary wrote:

I would use MySQL’s regex ability to grab only the rows you need to modify.

thanks mary. another stipulation:
from what i’ve read, since it seems the body/title/excerpt fields are mediumtext type and the collation of a default txp install seems to be _ci, does that mean i’m unable to use mysqls regex for case sensitive results?

basically i’d like to have an option in the plugin to support case-sensitive search and replace as well.

for example something like this SELECT * FROM `textpattern` WHERE title REGEXP '[[:<:]]test[[:>:]]' would return rows with titles containing ‘Test’ and ‘test’. there doesn’t seem to be an /i switch like php’s regex. i may head down jm’s php solution if thats the case..

Last edited by iblastoff (2007-11-30 13:57:12)

Mary · 2007-11-30 14:54:42

does that mean i’m unable to use mysqls regex for case sensitive results?

basically i’d like to have an option in the plugin to support case-sensitive search and replace as well.

Yes, but, it is still better to have more restricted results than fetching everything, isn’t it?

Fetches anything like ‘test’, may have rows we don’t want to manipulate:

SELECT * FROM `textpattern` WHERE title REGEXP '[[:<:]]test[[:>:]]';

Fetches everything, will definitely have rows we don’t want to manipulate:

SELECT * FROM `textpattern`;

iblastoff · 2007-11-30 17:44:13

good enough for me. i will apply all of this wholesome knowledge now. thanks :)

Textpattern CMS

Textpattern CMS support forum

#1 2007-11-28 23:31:13

sql 'smart' replace?

#2 2007-11-29 02:32:34

Re: sql 'smart' replace?

#3 2007-11-29 08:38:51

Re: sql 'smart' replace?

#4 2007-11-29 16:05:01

Re: sql 'smart' replace?

#5 2007-11-30 08:05:38

Re: sql 'smart' replace?

Sample data

PHP

#6 2007-11-30 13:15:05

Re: sql 'smart' replace?

#7 2007-11-30 13:53:59

Re: sql 'smart' replace?

#8 2007-11-30 14:54:42

Re: sql 'smart' replace?

#9 2007-11-30 17:44:13

Re: sql 'smart' replace?

Board footer