asked on

SQL command to search and replace characters in mySQL database

Good day everyone,

I am reposting this question with some clarifications and mentioning this in case you're experiencing a déjà vu.

This may be way too simple but it's a case of "not seeing the forest for the trees" with me.

I have a database that stores information in various languages. For some reason some letters with diacriticals got changed. My goal is to do a search all fields in a specific table of the database and replace parts of the content. I will be working in phpMyAdmin to access the database.

What needs to be changed is a specific letter in any part of any word. For instance I like to search for every instance of "Ã³"and replace it with ó -- That letter can be anywhere in a word or sentence.

Also, I don't want to do the search and replace by specific column as for instance "á" can be in Spanish, Slovak, Portuguese and Gaelic, and each language has a column in the table. I don't want to have to run this query individually for each language.

I wonder if you could help me with a SQL command, using this information:

database name: eurost
table name: fields_items
fields/columns (among others): value_catalan value_dutch value_french value_german value_italian value_romanian value_russian value_spanish

Thanks in advance for your help.

Richard

leakim971

have a look to this post : http://forums.mysql.com/read.php?98,246527,246527

sagonaholdings

ASKER

Thank you but the characters that appear in the database are not entity names. In fact I want to change characters that look like Ã¡ Ã© Ã* Ã³ u Ã¢ Ã® Ã´ Ã» to entity or decimal names.

The collation on the database is uf8_general_ci and I don't really want to change that.

leakim971

>You said : For instance I like to search for every instance of "Ã³"and replace it with ó

So, you just need to invert the attribute in the REPLACE function, for example :

#ampersand
IF INSTR( x , '&' )
THEN SET TextString = REPLACE(TextString, '&','&') ;
END IF ;

become :

#ampersand 
IF INSTR( x , '&' ) 
THEN SET TextString = REPLACE(TextString, '&','&amp;') ; 
END IF ;

Open in new window

sagonaholdings

ASKER

Sorry, I think I may have been a bit vague when asking for a SQL command to do a search and replace. I am NOT looking for information on how to solve the problem via PHP but rather something to the tune of:

UPDATE tablename SET tablefield = replace(tablefield,"findstring","replacestring");

But, I have not been able to figure out how to make this work for individual characters rather than an entire field

leakim971

What you saw in code snippet is part of a MySQL function.
http://dev.mysql.com/doc/refman/5.1/en/stored-routines.html
http://www.databasejournal.com/features/mysql/article.php/3569846/MySQL-Stored-Functions.htm

If you update (and why not rename the function) you will be able to run your query like this :

UPDATE tablename SET tablefield =  HTML_Encode(tablefield);

Open in new window

sagonaholdings

ASKER

I appreciate what you are doing and the suggestion to make my life easier but at this moment it's too complicated, I am NOT an experienced programmer, I have no idea how to do all that, all I am asking for (kindly, with a smile) is a simple solution to my query, helping me by using the database table and field names I supplied above in my original question.

I realize this is probably a ridiculously easy command I am looking for but I don't know how to put it together. So, anyone reading this and holding back thinking "this guy can't be asking for something THAT simple" please enlighten me.

Thanks!

sagonaholdings

ASKER

PS: please don't get caught up in the fact I am trying to change the encoding of a character. Let's just say, for argument's sake, that I am trying to change parts of misspelled words.

ASKER CERTIFIED SOLUTION

leakim971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sagonaholdings

ASKER

This is not the solution I was looking for, as my question was about something else. I am accepting this answer in order to close the question. Thank you for taking the time to help, I appreciate it.

leakim971

Thanks for the points! Happy new year!