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

Split value from one field to two(将值从一个字段拆分为两个)
本文介绍了将值从一个字段拆分为两个的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

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

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;

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

本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

相关文档推荐

ibtmp1是非压缩的innodb临时表的独立表空间,通过innodb_temp_data_file_path参数指定文件的路径,文件名和大小,默认配置为ibtmp1:12M:autoextend,也就是说在文件系统磁盘足够的情况下,这个文件大小是可以无限增长的。 为了避免ibtmp1文件无止境的暴涨导致
What does SQL clause quot;GROUP BY 1quot; mean?(SQL 子句“GROUP BY 1是什么意思?意思是?)
MySQL groupwise MAX() returns unexpected results(MySQL groupwise MAX() 返回意外结果)
MySQL SELECT most frequent by group(MySQL SELECT 按组最频繁)
Why Mysql#39;s Group By and Oracle#39;s Group by behaviours are different(为什么 Mysql 的 Group By 和 Oracle 的 Group by 行为不同)
MySQL GROUP BY DateTime +/- 3 seconds(MySQL GROUP BY DateTime +/- 3 秒)