updating tables

changing part of a data field

[sssweb]

Is it possible to update part of the text in a DB field, not the whole text?

I run a message board forum (similar in function to webmasterworld.com) and need to update the text in all posts for a particular value, while leaving the rest of the text in tact.

For example, say I want to update every instance of my board name from 'webmaster world' to 'webmaster universe', without changing the other text. So a post that currently reads:

"Welcome to webmaster world where you'll find all your site design questions answered....Be sure to tell your friends about webmaster world"

becomes:

"Welcome to webmaster universe where you'll find all your site design questions answered....Be sure to tell your friends about webmaster universe"

Any help?

[coopster]

Sure you can. You can either do it in an SQL query statement or you can pull a result set and process UPDATE statements with a server-side scripting language. Most databases will offer some form of function, for example MySQL has the REPLACE [dev.mysql.com] string function.

[sssweb]

Okay, I saw the REPLACE statement and I sort of follow you; what would be the SQL query for MySQL?

My table is named posts_table
The DB column is post_text

Something like:

SELECT REPLACE('webmaster world', 'world', 'universe');
UPDATE posts_table SET post_text...?;

(It's probably obvious that I don't know SQL)

[coopster]

First, make a backup copy of your data before you go and do any type of mass updates or deletions. ALWAYS remember to do this. I'll often create a temporary table to work on first instead. Then you just perform the update:

UPDATE posts_table SET post_text = REPLACE (post_text, 'webmaster world', 'webmaster universe');

[sssweb]

Gee things are simple when you know what you're doing -- thanks. I'll work on it tomorrow and post if I have a glitch.

Can I use '%' as a wildcard in the REPLACE section?

[sssweb]

Two questions:

1) Is this a valid SQL query for MySQL (note, among other things, the wildcard '%' ending the LIKE statement):

UPDATE table_name SET col_name = REPLACE (col_name, 'old text', 'new text') WHERE post_text LIKE 'This is old text%';

2) If so, and my DB record now contains:

This is old text.
This is old text too.
This is my old text.

which will be the updated query result:

a:

This is new text.
This is new text too.
This is my old text.

or b:

This is new text.
This is new text too.
This is my new text.

(In other words, does WHERE return TRUE if the LIKE statement is found anywhere in the record - giving result b - or does it give a distinct result for each 'old text' it finds, giving result a?)

[coopster]

Can I use '%' as a wildcard in the REPLACE section?

No. It is going to be treated as a literal character, the percent sign.

Yes, that is a valid SQL statement (except that I'm guessing

col_name

would likely be the name of your column,

post_text

).

And

a)

being the result is indeed correct, that is exactly what would be updated. The text would be matched exactly until it reaches wildcard characters. So the comparison looks for the exact same sentence until it reaches the percent wildcard character and accepts anything after that.

Some helpful resources:
[dev.mysql.com...]
[dev.mysql.com...]

[sssweb]

How do I do a literal search for \" (backslash quote) in a string?

I've tried using escape characters (backslashes), then I saw in the MySql manual that you need to double backslashes in LIKE statements (which I'm using), but I still can't get it to work.

My latest attempt is:

SELECT * FROM db_table WHERE db_column LIKE '%\\\\\\"%';

The correct query should return positive for an entry like:

This is sample text with the string \" in it.

If it's easier, is it possible to designate a different escape character in the query, so as not to confuse it with the backslash I'm searching for? If so, what would that query be?