Splitting a column that contains Chinese Characters MySQL-CodePudding

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 ?

CodePudding user response：

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: