Making string replacements on all strings in a column 2

[Quimbly]

I want to make a string replacement (and update) to all strings in a column of a table. In other words, for every string in a column of a table of my MySQL DB, I want to replace a substring in those strings with a new value.

I've found the REPLACE SQL function, but I don't know the syntax to apply to all strings in the column.

Can someone help?

 

[r937]

what do you mean by "all strings in the column"?

could you give an example please?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[Quimbly]

Ok, here's the deal. I'm running a MySQL DB for my website. In the database, I have a table for quotes with two columns: id and quote_text. For all rows in that table, I want to replace any instance of the substring
"- " with "-", in the quote_text column.

So, here's a sample from the 'quotes' tables:

id quote_text
1 "quote text 1 - Author1"
2 "quote text 2 - Author2"
3 "quote text 3 - Author3"
...

When I'm done, I want to have the following:

id quote_text
1 "quote text 1 -Author1"
2 "quote text 2 -Author2"
3 "quote text 3 -Author3"
...


Get it?

 

[PHV]

[tt]REPLACE(quote_text, '- ', '-')[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Quimbly]

Sorry, I'm new to SQL and MySQL. I can't seem to get this to work.

The table name is: quote
The column name is: quote_display

I've tried running several different versions:

REPLACE (`quote_display`, '- ', '-');
REPLACE ('quote_display', '- ', '-');
REPLACE ("quote_display", '- ', '-');
REPLACE (quote_display, '- ', '-');

Won't I need to include the table name in there somewhere?

 

[PHV]

UPDATE yourTable SET quote_display = REPLACE (quote_display, '- ', '-')
WHERE quote_display LIKE '%- %'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JarlH]

UPDATE quote
SET quote_display = REPLACE (quote_display, '- ', '-');

Note that REPLACE is not an ANSI/ISO standard function. However its supported by most DBMS products, since its defined by the ODBC API.

 

[JamesLean]

UPDATE quote
SET quote_display = REPLACE(quote_display, '- ', '-')

--James

 

[r937]

only PHV remembered the imporance of including a WHERE clause!!

star for you

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[JamesLean]

The WHERE clause would only be important if some values didn't contain the search string. If all rows did contain it, which the supplied example data seemed to suggest, then the WHERE clause would actually reduce performance! ;-)

--James

 

[r937]

good point

star for you too, eh



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[Quimbly]

Thanks, guys. That did it!

 

[JarlH]

Excuse my ignorance, but why is that WHERE clause so important when just a few of the rows are going to be updated?

Is LIKE usually much faster than REPLACE or what?

 

[r937]

the importance is, you do not want all rows updated, including the ones that don't contain the string

it's not a question of the speed of a WHERE clause, it's a question of filling up the log file and doing unnecessary updates



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)