将值从一个字段拆分为两个 [英] Split value from one field to two

问题描述

我有一个表字段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?

所有记录的格式均为姓氏姓氏"(不带引号，并且中间没有空格).

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:

Federico Cargnelutti的
• MySQL拆分字符串函数

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

这篇关于将值从一个字段拆分为两个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！