CODEWITHSUNDEEP
mysqlcharacter-encodingjoomlacollation
Jinx
Jinx

Reputation: 867

How to replace single character in large MySQL tables

I had some problems with upgrading joomla from 1.0 to 1.5 (I have to go all the way to 1.7 ;).

Anyway, my client has an old joomla 1.0 instalation and the site is hudge and in Croatian. That means I have to deal with characters like Č,č,Ć,ć,Đ,đ... Old database is in latin1_swedish_ci collation, and I've transferred it with migration script to new database of joomla 1.5 that is in utf8_general_ci.

That resulted (as expected) in some character confusion, so for example: ć became è, È --> Č, etc...

Converting 1.0 database to utf8 collation is not an option, since it cuts off rest of the content on the first appearance of Č,ć,đ,đ etc...

So, what I was doing is this query:

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

What this does is get the joomla content table and in all intro texts replaces È with Č.

I did this for titles as well, but when I try to do it with fulltext, 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 issue (since this is a full text after all) or am I just doing something wrong. Also, if there's a better way to replace all this characters please tell me, this is what I figured out from MySQL "unreadable" documentation.

Upvotes: 4

Views: 3814

Answers (1)

JK.
JK.

Reputation: 5136

FULLTEXT is a reserved word in mysql and need to be escaped by back ticks (`) if your column name have the same name.

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

Upvotes: 6

Related Questions