?
Solved

SQL command to search and replace characters in mySQL database

Posted on 2010-01-09
10
Medium Priority
?
1,015 Views
Last Modified: 2013-11-16
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
0
Comment
Question by:sagonaholdings
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 82

Expert Comment

by:leakim971
ID: 26274560
0
 

Author Comment

by:sagonaholdings
ID: 26274631
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.
0
 
LVL 82

Expert Comment

by:leakim971
ID: 26275677
>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, '&','&') ; 
END IF ;

Open in new window

0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 

Author Comment

by:sagonaholdings
ID: 26275914
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
0
 
LVL 82

Expert Comment

by:leakim971
ID: 26275955
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

0
 

Author Comment

by:sagonaholdings
ID: 26275987
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!
0
 

Author Comment

by:sagonaholdings
ID: 26275994
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.
0
 
LVL 82

Accepted Solution

by:
leakim971 earned 2000 total points
ID: 26276038
No, it's not a ridiculous easy command. You need to create a function and use this function. I proposed you a function that do the invert job of what you want (If I understood).

You will need to modify this function, and give you a sample of what to do. The current function replace, in a submitted field, HTML entities by their special character equivalent.
For example you pass p&eacutep&eacute and it should return pépé

part of the code doing this job in the procedure (http://forums.mysql.com/read.php?98,246527,246527) :

#small e, acute accent
IF INSTR( x , 'é' )
THEN SET TextString = REPLACE(TextString, 'é','é') ;
END IF ;

You want the invert job (If I understood :) so :

#small e, acute accent
IF INSTR( x , 'é' )
THEN SET TextString = REPLACE(TextString, 'é','é') ;
END IF ;

If this is really what you want, if I understood your need, you need to update the procedure (lot of job because lot of characters)
0
 

Author Closing Comment

by:sagonaholdings
ID: 31675008
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.
0
 
LVL 82

Expert Comment

by:leakim971
ID: 26276178
Thanks for the points! Happy new year!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question