How to insert text before and after text

using php/mysql

[twist]

Using MySQL 4.0.18

I have a type(text) column that is already filled with text. What I need to do is insert some new text around the text already there.

Example,

old text in text column

needs to be,

More old text in text column now

I also need to only add the new words "More" and "now" only where they don't already exist.

[grandpa]

How many records are you dealing with?

If it's more than a couple of hundred I'd write a script to read each record, re-write the field data and update the records. Anything less then it's just as easy to use myphpAdmin (or something similar) and manually change the data.

If the row has a unique ID I might read the table first and load the columns into an array, then loop the array and make those changes and update each record in the loop. Each array element would contain the unique ID and the column to be changed. That makes updating a lot safer.

[tomda]

Grandpa gave you the solution...

But I do see the point of changing the database especially if you add text before and after your value ONLY... If it was in the text itself I would understand it.

I thwould write a small function that add text and call it when you output the data row. Well, something like this if it is not clear.

function add($var) {$var = "More ".$var." now.";
return $var;}

$text="your text";
echo add($text);

[twist]

Using phpmyadmin's sql query box, the following works for concatenating,

UPDATE `database` SET `text` = CONCAT('More',`text`,'now')

but I am having problems with using a substring check for "More" and "now" already existing, the following is giving me an error,

UPDATE `database` SET `text` = CONCAT('More',`text`,'now') WHERE SUBSTRING(`text`,0,4) != 'More'

[coopster]

An error? Or are you getting an empty string in your comparison, which seems more likely.

For functions that operate on string positions, the first position is numbered 1.

Resource:
[dev.mysql.com...]

Replace that zero with a 1 in your SUBSTRING function:

... SUBSTRING(`text`,1,4)!= 'More' 

[killroy]

why not try:

UPDATE table SET field=CONCAT('More',field) WHERE field NOT LIKE "More%"

[twist]

For functions that operate on string positions, the first position is numbered 1.

Thanks, I was wondering about that.

And thanks killroy, very easy way to do it.