将值从一个字段拆分为两个

2021-11-20 00:00:00 split mysql

我有一个表字段 membername，其中包含用户的姓氏和名字.是否可以将它们分成 2 个字段 memberfirst、memberlast?

I've got a table field membername which contains both the last name and the first name of users. Is it possible to split those into 2 fields memberfirst, memberlast?

所有记录的格式都是Firstname Lastname"(没有引号，中间有空格).

All the records have this format "Firstname Lastname" (without quotes and a space in between).

推荐答案

遗憾的是 MySQL 没有拆分字符串功能.但是，您可以为此创建一个用户定义的函数，如下文所述:

Unfortunately MySQL does not feature a split string function. However you can create a user defined function for this, such as the one described in the following article:

  • MySQL 拆分字符串函数 by Federico Cargnelutti
  • MySQL Split String Function by Federico Cargnelutti

使用该功能:

DELIMITER $$

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN 
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');
END$$

DELIMITER ;

您可以按如下方式构建查询:

you would be able to build your query as follows:

SELECT SPLIT_STR(membername, ' ', 1) as memberfirst,
       SPLIT_STR(membername, ' ', 2) as memberlast
FROM   users;

如果您不想使用用户定义的函数并且不介意查询更冗长，您还可以执行以下操作:

If you prefer not to use a user defined function and you do not mind the query to be a bit more verbose, you can also do the following:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 1), ' ', -1) as memberfirst,
       SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 2), ' ', -1) as memberlast
FROM   users;

相关文章