Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1038
  • Last Modified:

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
0
sagonaholdings
Asked:
sagonaholdings
  • 5
  • 5
1 Solution
 
leakim971PluritechnicianCommented:
0
 
sagonaholdingsAuthor Commented:
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
 
leakim971PluritechnicianCommented:
>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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sagonaholdingsAuthor Commented:
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
 
leakim971PluritechnicianCommented:
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
 
sagonaholdingsAuthor Commented:
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
 
sagonaholdingsAuthor Commented:
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
 
leakim971PluritechnicianCommented:
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
 
sagonaholdingsAuthor Commented:
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
 
leakim971PluritechnicianCommented:
Thanks for the points! Happy new year!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now