Page 1 of 1
MySQL str_replace
Posted: Tue Nov 18, 2008 8:32 am
by icesolid
Is it possible to run a MySQL query to replace all of one character to something else. Some thing similar to this function in PHP, but instead a MySQL query:
Code: Select all
str_replace("word_to_replace", "replacement_word", $string);
This method is OK but to convert all of the words in all of the fields in a table using UPDATE would take forever.
Re: MySQL str_replace
Posted: Tue Nov 18, 2008 8:38 am
by VladSun
Re: MySQL str_replace
Posted: Tue Nov 18, 2008 8:49 am
by icesolid
Well I see the REPLACE function. However, that only works on a field correct? Is there anyway to just run that query for the whole table?
Re: MySQL str_replace
Posted: Tue Nov 18, 2008 9:03 am
by VladSun
icesolid wrote:Well I see the REPLACE function. However, that only works on a field correct? Is there anyway to just run that query for the whole table?
Clarify, please.
Does "whole table" mean "all the values in a table column"?
Re: MySQL str_replace
Posted: Tue Nov 18, 2008 9:09 am
by icesolid
The whole table means every field in the table. The REPLACE function seems to only allow you to replace the instances in one field.
Re: MySQL str_replace
Posted: Tue Nov 18, 2008 9:14 am
by VladSun
So... how do you imagine your UPDATE query? Write it down, please - it doesn't have to be correct.
Re: MySQL str_replace
Posted: Tue Nov 18, 2008 9:21 am
by icesolid
This is would I would like to perform:
Code: Select all
UPDATE `table` REPLACE(`ALL COLUMNS IN TABLE`, 'word', 'new_word');
VS:
Code: Select all
UPDATE `table` REPLACE(`COLUMN`, 'word', 'new_word');
Re: MySQL str_replace
Posted: Tue Nov 18, 2008 9:23 am
by VladSun
No, you can't.
You should extend you query for every column you need to update.
Re: MySQL str_replace
Posted: Tue Nov 18, 2008 9:25 am
by icesolid
So I should just use a PHP script then, that loops through all of the columns. I was trying to avoid that.
Re: MySQL str_replace
Posted: Tue Nov 18, 2008 9:39 am
by VladSun
No - you are not obligated to ...
[sql]UPDATE peopleSET name = REPLACE(name, 'foo', 'bar'), surname = REPLACE(surname, 'foo', 'bar')[/sql]
Re: MySQL str_replace
Posted: Tue Nov 18, 2008 9:41 am
by icesolid
Thats not a bad idea, but still, ten of the tables are for long insurance inspection forms, with over 300 questions, which means 300 columns, that will be one hell of a query, but your method posted above will be nicer than a PHP loop

.
Thanks for your time

Re: MySQL str_replace
Posted: Tue Nov 18, 2008 9:50 am
by VladSun
icesolid wrote:Thats not a bad idea, but still, ten of the tables are for long insurance inspection forms, with over 300 questions, which means 300 columns, that will be one hell of a query, but your method posted above will be nicer than a PHP loop

.
Thanks for your time


Dynamically created queries.
[sql]SELECT * FROM information_schema.COLUMNS;[/sql]
and use a stored procedure/PHP code to loop over columns of a string type
EDIT: Sorry, wrong table.
Re: MySQL str_replace
Posted: Tue Nov 18, 2008 10:14 am
by icesolid
Thanks, you have been a help.
I got myself into all of this when I had to start using UTF-8 for some international symbols that my company starting using. I was using ISO-8859-1 prior to the switch. Well everything went OK, however, MS Word curly quotes and other special characters that MS Word uses do not sit well when you display them in a textarea on a UTF-8 page when they were submitted to the database via ISO-8859-1 encoding. All of the other characters are just fine.
So I am going through and removing all of the MS Word special characters because they are causing some problems when people would go to update a textarea field and have some crazy mumbo jumbo instead of MS Word's curly quotes.
In the future I will always start off with UTF-8, and hope one day Microsoft will get with the rest of us and try to comply better with standards. By the way, IE sucks, but thats another story.