MySQL str_replace

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

MySQL str_replace

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL str_replace

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: MySQL str_replace

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL str_replace

Post 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?
8O
Clarify, please.
Does "whole table" mean "all the values in a table column"?
There are 10 types of people in this world, those who understand binary and those who don't
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: MySQL str_replace

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL str_replace

Post by VladSun »

So... how do you imagine your UPDATE query? Write it down, please - it doesn't have to be correct.
There are 10 types of people in this world, those who understand binary and those who don't
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: MySQL str_replace

Post 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');
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL str_replace

Post by VladSun »

No, you can't.
You should extend you query for every column you need to update.
There are 10 types of people in this world, those who understand binary and those who don't
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: MySQL str_replace

Post by icesolid »

So I should just use a PHP script then, that loops through all of the columns. I was trying to avoid that.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL str_replace

Post by VladSun »

No - you are not obligated to ...
[sql]UPDATE    peopleSET    name = REPLACE(name, 'foo', 'bar'),    surname = REPLACE(surname, 'foo', 'bar')[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: MySQL str_replace

Post 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 :banghead: .

Thanks for your time :D
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL str_replace

Post 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 :banghead: .

Thanks for your time :D
;)
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.
There are 10 types of people in this world, those who understand binary and those who don't
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: MySQL str_replace

Post 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.
Post Reply