How to replace a single character in large MySQL tables

Question

I am having problems updating joomla from 1.0 to 1.5 (I need to go all the way to 1.7;).

In any case, my client has an old installation of joomla 1.0, and the site is hudge in Croatian too. This means that I have to deal with characters like Č, č, Ć, ć, Đ, đ ... The old database is in the latin1_swedish_ci setting, and I transferred it with the script transfer to the new joomla 1.5 database, which located in utf8_general_ci.

This led (as expected) to some confusion of character, for example: ć became è, È → Č, etc.

Converting a 1.0 database to utf8 is not an option, as it cuts off the rest of the content when Č, ć, đ, đ, etc., first appears.

So what I did was this request:

update jos_content set introtext = replace(introtext, 'È', 'Č'); 

What it is to get a joomla content table and replace with Č in all introductory texts.

I did this for the headers too, but when I try to do this with the full text, I get this error:

 Error SQL query: UPDATE jos_content SET FULLTEXT = REPLACE( introtext, 'È', 'Č' ) ; MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fulltext = replace(introtext, 'È', 'ÄŒ')' at line 2 

SO, is this some kind of memory problem (since it's the full text) or I'm just doing something wrong. Also, if there is a better way to replace all of these characters, please tell me what I understood from the MySQL “unreadable” documentation.

Answer 1

FULLTEXT is a reserved word in mysql and should be escaped with back ticks (`) if your column name has the same name.

 UPDATE jos_content SET `FULLTEXT` = REPLACE(introtext, 'È', 'Č');