CODEWITHSUNDEEP
mysqldatabasesql-server-collation
Ajay Chauhan
Ajay Chauhan

Reputation: 1

How to convert latin1_swedish_ci collation string into hindi text

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="अपने पà¥à¤¯à¤¾à¤° से अपनी दिल की बात कहेà¤";

Upvotes: 0

Views: 89

Answers (1)

user1191247
user1191247

Reputation: 12973

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);

Upvotes: 0

Related Questions