Search code examples
mysqlindexingsubstringright-to-leftpersian

MYSQL: after using SUBSTRING_INDEX my data change/corrupt (rtl language)

After using substring_index on a column, my data changes or corrupts the non-English text in the field, which is in Persian. I also checked the collation and charset and it is UTF-8.

If I use English, it works like a charm, but in rtl languages it doesn't work. Here is my record before substring:

select group_id , rows from concat

before

Here is what I get after substring_index:

select group_id , SUBSTRING_INDEX(rows, ',', 1) as name from concat

after

It shows "A+3" but
It should show "فثس".

Anyone know a solution?

Solution

  • Actually, I figured it out; The problem was after using substring_index the field type changes to "MEDIUMBLOB" which causes the problem. so I did converted it and now it works.

    select group_id , CONVERT(SUBSTRING_INDEX(rows, ',', 1), CHAR(1000)) as name from concat