SUBSTRING_INDEX() on chinese text not working

[deansatch]

I am busy translating a site into chinese and one part which uses SUBSTRING_INDEX(CONTENT, ' ', 16) AS CONTENT doesn't seem to be working as expected. It works on the English version but it shows everything in the Chinese version.

 

Do I need to tell MYSQL that its using chinese characters or something?

[deansatch]

Ah. It's because there are no spaces between the characters/words/paragraphs. I tried LEFT(CONTENT, 100) AS CONTENT which works but sometimes the last character is the dreaded diamond with a question mark in it.

 

Any ideas???

[deansatch]

Arrgh! The text is going into my database as chinese characters and coming out as chinese characters..but in phpmyadmin it looks like a load of gibberish surrounded by html tags.

 

Clearly i am missing something vital here

[deansatch]

Ok...I am now assuming that LEFT() is producing a [?] symbol because it is counting the number of characters in the database and because several symbols make up a chinese character, it is making the last one a half-done, none-recognisable thing.

 

So...is the fact the data is stored in mysql random looking symbols correct? And if so, how can I use functions like LEFT()?

[fenway]

Chinese is likely being stored as multi-byte characters -- either 2 or 3 -- and if it's 3, 100 won't work.  At least pick a number that's a multiple of both 2 and 3.

[deansatch]

Ah..thanks. I kept ploughing through it and eventually used mb_substr() in php instead. Not ideal, but it works.