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

我得到了一个表字段 membername，它包含用户的姓和名。有没有可能把它们分成2个字段 memberfirst，memberlast？

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

299443 次浏览
小开
最佳答案

不幸的是，MySQL 没有分割字符串函数。然而，您可以为此创建一个 用户自定义函数，如下面文章中描述的:

有了这个功能:

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 ;

您将能够构建如下查询:

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

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

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

您可能需要这样一个函数的唯一情况是一个 UPDATE 查询，它将更改您的表以将 Firstname 和 Lastname 存储到单独的字段中。

数据库设计必须遵循一定的规则，数据库规范化是其中最重要的规则之一

小开

用这个

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX( `membername` , ' ', 2 ),' ',1) AS b,
SUBSTRING_INDEX(SUBSTRING_INDEX( `membername` , ' ', -1 ),' ',2) AS c FROM `users` WHERE `userid`='1'
小开

如果你的计划是这样做作为一个 查询,的一部分请 不要做那 (a)。说真的，这是个性能杀手。在某些情况下，您可能并不关心性能(比如一次性的迁移作业来分割字段，以便在将来提供更好的性能) ，但是，如果您经常为任何东西而不是一个米老鼠数据库这样做，那么您就是在浪费资源。

如果您发现自己必须以某种方式仅处理列的一部分，那么您的 DB 设计是有缺陷的。它可能在家庭地址簿、食谱应用程序或任何其他小型数据库上运行良好，但不能扩展到“真正的”系统。

将名称的组件存储在单独的列中。通过简单的连接(当您需要全名时)将列连接在一起几乎总是比通过字符搜索将它们分开快得多。

如果由于某种原因无法拆分字段，至少应该添加额外的列，并使用插入/更新触发器来填充它们。虽然不是3NF，但这将保证数据仍然是一致的，并将大大加快您的查询。您还可以确保额外的列同时是小写的(如果您正在搜索它们，则可以对它们进行索引) ，这样就不必处理大小写问题。

而且，如果您甚至不能添加列和触发器，请注意(如果是为客户机添加的，则要让客户机知道)它是不可伸缩的。

(a) 当然，如果您的意图是将这个查询用于 修好模式，以便将名称放置在 桌子而不是 查询,中的单独列中，我认为这是一种有效的使用方法。但是我重申，在查询中这样做并不是一个好主意。

小开

虽然没有完全回答这个问题，但是面对同样的问题，我最终做到了这一点:

UPDATE people_exit SET last_name = SUBSTRING_INDEX(fullname,' ',-1)
UPDATE people_exit SET middle_name = TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(fullname,last_name,1),' ',-2))
UPDATE people_exit SET middle_name = '' WHERE CHAR_LENGTH(middle_name)>3
UPDATE people_exit SET first_name = SUBSTRING_INDEX(fullname,concat(middle_name,' ',last_name),1)
UPDATE people_exit SET first_name = middle_name WHERE first_name = ''
UPDATE people_exit SET middle_name = '' WHERE first_name = middle_name
小开

SELECT 变量(未创建用户定义函数) :

SELECT IF(
LOCATE(' ', `membername`) > 0,
SUBSTRING(`membername`, 1, LOCATE(' ', `membername`) - 1),
`membername`
) AS memberfirst,
IF(
LOCATE(' ', `membername`) > 0,
SUBSTRING(`membername`, LOCATE(' ', `membername`) + 1),
NULL
) AS memberlast
FROM `user`;

这种方法还考虑到:

  • Memname 没有空格的值: 它会将整个字符串添加到 member first，并将 member last 设置为 NULL。
  • Member name 具有多个空格的值: 它会将第一个空格之前的所有内容添加到 member first，将其余内容(包括其他空格)添加到 member last。

更新版本如下:

UPDATE `user` SET
`memberfirst` = IF(
LOCATE(' ', `membername`) > 0,
SUBSTRING(`membername`, 1, LOCATE(' ', `membername`) - 1),
`membername`
),
`memberlast` = IF(
LOCATE(' ', `membername`) > 0,
SUBSTRING(`membername`, LOCATE(' ', `membername`) + 1),
NULL
);
小开

Mysql 5.4提供了一个本机拆分函数:

SPLIT_STR(<column>, '<delimiter>', <index>)
小开

我有个专栏，名和姓都在一个专栏里。名和姓之间用逗号隔开。下面的代码起作用了。没有错误检查/更正。只是一个愚蠢的分裂。使用 phpMyAdmin 执行 SQL 语句。

UPDATE tblAuthorList SET AuthorFirst = SUBSTRING_INDEX(AuthorLast,',',-1) , AuthorLast = SUBSTRING_INDEX(AuthorLast,',',1);

13.2.10更新语法

小开

它从这里获取 smhg，从 MySQL 中给定子字符串的最后一个索引获取 curt，然后将它们组合。这是为 mysql 准备的，我所需要做的就是将 name 和 first _ name last _ name 进行合适的分割，最后一个名字是一个单词，在这个单词之前的所有名字都是名字，名字可以是 null，1个单词，2个单词，或者超过2个单词。即: 无效; 玛丽; 玛丽史密斯; 玛丽 A 史密斯; 玛丽苏埃伦史密斯;

因此，如果 name 为一个单词或 null，则 last _ name 为 null。如果 name 大于1个单词，则 last _ name 为 last word，first _ name 为 last word 之前的所有单词。

请注意，我已经手动删除了像 Joe Smith Jr. 、 Joe Smith Esq 等等的内容，这当然很痛苦，但是它足够小，所以在决定使用哪个方法之前，您需要确保真正查看 name 字段中的数据。

注意，这也会减少结果，所以名字前面或后面不会有空格。

我只是把这张照片发给其他人他们可能会在谷歌上搜索我需要的东西。当然，这是可行的，首先使用 select 对其进行测试。

这是一次性的，所以我不在乎效率。

SELECT TRIM(
IF(
LOCATE(' ', `name`) > 0,
LEFT(`name`, LENGTH(`name`) - LOCATE(' ', REVERSE(`name`))),
`name`
)
) AS first_name,
TRIM(
IF(
LOCATE(' ', `name`) > 0,
SUBSTRING_INDEX(`name`, ' ', -1) ,
NULL
)
) AS last_name
FROM `users`;




UPDATE `users` SET
`first_name` = TRIM(
IF(
LOCATE(' ', `name`) > 0,
LEFT(`name`, LENGTH(`name`) - LOCATE(' ', REVERSE(`name`))),
`name`
)
),
`last_name` = TRIM(
IF(
LOCATE(' ', `name`) > 0,
SUBSTRING_INDEX(`name`, ' ', -1) ,
NULL
)
);
小开

方法，当数据全部到达 first _ name 字段时，我用它将 first _ name 分成 first _ name 和 last _ name。这将只把最后一个单词放在姓氏字段中，因此“ john phillips sousa”将是“ john phillips”的名字和“ sousa”的姓。它还可以避免覆盖任何已经修复的记录。

set last_name=trim(SUBSTRING_INDEX(first_name, ' ', -1)), first_name=trim(SUBSTRING(first_name,1,length(first_name) - length(SUBSTRING_INDEX(first_name, ' ', -1)))) where list_id='$List_ID' and length(first_name)>0 and length(trim(last_name))=0
小开

现有的回答似乎过于复杂，或者不是对这个特定问题的严格回答。

我认为，简单的答案是下面这个问题:

SELECT
SUBSTRING_INDEX(`membername`, ' ', 1) AS `memberfirst`,
SUBSTRING_INDEX(`membername`, ' ', -1) AS `memberlast`
;

我认为在这种特殊情况下没有必要处理超过两个单词的名称。如果你想正确地做到这一点，在某些情况下，分裂可能是非常困难的，甚至是不可能的:

  • 约翰 · 塞巴斯蒂安 巴赫
  • 约翰 · 沃尔夫冈 歌德
  • Edgar 爱伦坡
  • 雅各布 · 路德维格 · 菲利克斯
  • Pet fi 桑德尔
  • Vendelné 法卡斯 Margit
  • 黒澤 明

在一个设计合理的数据库中，人名应该以部分和整体的形式存储。当然，这并不总是可能的。

小开 
UPDATE `salary_generation_tbl` SET
`modified_by` = IF(
LOCATE('$', `other_salary_string`) > 0,
SUBSTRING(`other_salary_string`, 1, LOCATE('$', `other_salary_string`) - 1),
`other_salary_string`
),
`other_salary` = IF(
LOCATE('$', `other_salary_string`) > 0,
SUBSTRING(`other_salary_string`, LOCATE('$', `other_salary_string`) + 1),
NULL
);
小开

在 MySQL 中使用这个选项:

SELECT Substring(nameandsurname, 1, Locate(' ', nameandsurname) - 1) AS
firstname,
Substring(nameandsurname, Locate(' ', nameandsurname) + 1)    AS lastname
FROM   emp
小开

以防有人需要碾过一张桌子，然后分割一个字段:

  1. 首先，我们使用 上面提到的功能:
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_split_str`($str VARCHAR(800), $delimiter VARCHAR(12), $position INT) RETURNS varchar(800) CHARSET utf8
DETERMINISTIC
BEGIN
RETURN REPLACE(
SUBSTRING(
SUBSTRING_INDEX($str, $delimiter, $position),
LENGTH(
SUBSTRING_INDEX($str, $delimiter, $position -1)
) + 1
),
$delimiter, '');
END
  1. 其次，我们在字符串上运行 while 循环，直到没有任何结果(我为 JOIN 子句添加了 $id) :
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_split_str_to_rows`($id INT, $str VARCHAR(800), $delimiter VARCHAR(12), $empty_table BIT) RETURNS int(11)
BEGIN


DECLARE position INT;
DECLARE val VARCHAR(800);
SET position = 1;
    

IF $empty_table THEN
DROP TEMPORARY TABLE IF EXISTS tmp_rows;
END IF;
            

SET val = fn_split_str($str, ',', position);
            

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_rows AS (SELECT $id as id, val as val where 1 = 2);
        

WHILE (val IS NOT NULL and val != '') DO
INSERT INTO tmp_rows
SELECT $id, val;
        

SET position = position + 1;
SET val = fn_split_str($str, ',', position);
END WHILE;
    

RETURN position - 1;
END
  1. 最后我们可以这样使用它:
DROP TEMPORARY TABLE IF EXISTS tmp_rows;
SELECT  SUM(fn_split_str_to_rows(ID, FieldToSplit, ',', 0))
FROM    MyTable;


SELECT * FROM tmp_rows;

您可以使用 id 连接到其他表。

如果只拆分一个值，那么可以这样使用它

SELECT  fn_split_str_to_rows(null, 'AAA,BBB,CCC,DDD,EEE,FFF,GGG', ',', 1);
SELECT * FROM tmp_rows;

我们不需要清空临时表，函数会处理这个问题。


提交答案