Search code examples
mysqlsubstringcjk

Splitting a column that contains Chinese Characters MySQL

So I have a table that has a column 'full_name' and it stores chinese names in the format surname+given name.

What I want to do is split it into two columns: one for surname and one for given name. The surname is always one character and the given name is either one or two characters long. no spaces between. how can I split them?

When I tried to use: SELECT SUBSTRING(full_name, 0, 1) AS surname FROM citizens_CH it returned an empty column.. I read somewhere that Chinese characters in MySQL aren't actually of length one .. but no matter what I changed it didnt do anything. Any advice ?

Solution

  • Hello Please test this:

    First create a table:

    CREATE TABLE ChineseNames (FullName VARCHAR(5));
INSERT INTO ChineseNames VALUES
('王沐宸'),
('李浩宇'),
('张沐辰'),
('刘茗泽'),
('杨奕辰'),
('黄宇泽'),
('赵浩然'),
('吴奕泽'),
('王沐');

    Then Let's write the code:

    SELECT FullName, LEFT(FullName,1) AS SurName,SUBSTRING(FullName,2,9999) AS GivenName
FROM ChineseNames;

    Result It produces:

    GHJ