0

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

Ajay Chauhan
  • 1
  • Collation is rules set which is applied while the strings are compared. Maybe you need in CHARACTER SET convertion? if so then read about CONVERT(.. USING ..) function. – Akina Mar 02 '23 at 12:50
  • `ALTER TABLE my_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;` – iamafish Mar 02 '23 at 13:02

1 Answers1

0

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);
user1191247
  • 10,808
  • 2
  • 22
  • 32