How to convert latin1_swedish_ci collation string into hindi text

Question

Hi Everyone i am facing a problem with string collation change as i have millions of rows in latin1_swedish_ci collation and i wants to convert the reocords to hindi text and store in utf8_unicode_ci

$string="अपने पà¥à¤¯à¤¾à¤° से अपनी दिल की बात कहेà¤";

$string="अपने पà¥à¤¯à¤¾à¤° से अपनी दिल की बात कहेà¤";

Answer 1

There are a few different ways you can end up with multibyte data stored in a latin1 column. The simplest case is when your client application has a utf8 connection and the data is written to the latin1 column.

If this is the case, you should be able to view the content correctly with:

SELECT CONVERT(CAST(latin1_col AS BINARY) USING utf8mb4) FROM tbl;

If this query returns the text as desired, add a new column to your table, populate it and verify it, before dropping or modifying the existing latin1 column:

ALTER TABLE tbl ADD COLUMN new_utf8_col VARCHAR(1024) CHARACTER SET utf8mb4;
UPDATE tbl SET new_utf8_col = CONVERT(CAST(latin1_col AS BINARY) USING utf8mb4);