关于mysql：在SQL中将单列(名称)分为两列(姓氏，姓氏)

2024-07-10 22:50| 来源: 网络整理| 查看: 265

目前，我正在进行数据库重新设计项目。该项目的大部分工作是从旧数据库中提取数据并将其导入新数据库中。

旧数据库中表中的列之一称为"名称"。它在一个字段(ugh)中都包含一个姓氏和一个姓氏。新表有两列。名字和姓氏。我需要提出一种干净有效的方法来将这一列分为两部分。

现在，我想在同一个表中进行所有操作，然后就可以轻松地将其转移。

3栏：

姓名(姓和名) 姓氏(当前为空，姓名的前半部分应放在此处) 姓(当前为空，名字的后半部分应放在此处)

我需要做的：将名字一分为二，然后将其分为姓氏和姓氏

如果有人可以阐明如何执行此类操作，我将不胜感激，因为我之前从未在SQL中进行过此类操作。

数据库引擎：MySQL 存储引擎：InnoDB

相关讨论我认为，只要有sql标记，stackoverflow就应该询问哪个rdbms作为建议标记选项。 stackoverflow.com/questions/2647/split-string-in-sql @ray-所有答案似乎都是特定于MSSQL的，或者要求MySQL用户通常无法访问的功能(例如递归CTE)。也许是重复的问题？ stackoverflow.com/questions/14950466/ @Giupo是的，可能-我继续回答了它，因为还有很多其他信息可以提供高质量的答案，而您在这里找不到。您想如何分割Ludwig van Beethoven？还是John F. Kennedy？不要谈论其他"不同"的语言/文化... @joop幸运的是，这不是问题，否则我会在问题中进行说明。另请参阅stackoverflow.com/questions/1122328/ 假设每个人都有一个姓氏和名字是不正确的。

一个快速的解决方案是使用SUBSTRING_INDEX将所有内容都保留在第一个空格的左边，并将所有内容都保留在第一个空格之后：

1234UPDATE tablename SET Forename = SUBSTRING_INDEX(Name, ' ', 1), Surname = SUBSTRING_INDEX(Name, ' ', -1)

请在这里看小提琴。它不是完美的，因为一个名称可以有多个空格，但是开头可能是一个很好的查询，并且适用于大多数名称。

相关讨论我认为这是最接近我想要的疾病。感谢您的发布！

尝试这个：

123456insert into new_table (forename, lastName, ...) select substring_index(name, ' ', 1), substring(name from instr(name, ' ') + 1), ... from old_table

假设第一个单词是姓氏，其余的是姓氏，则可以正确处理多词姓氏，例如" John De Lacey"

相关讨论众所周知，您接受的答案可能存在错误。对于" John De Lacey"之类的宝贝，其他答案将丢弃除了第一个和最后一个单词以外的所有单词。因此，答案会将第一个单词放入"姓氏"列DVD中，将其余单词(无论多少单词)放入" lastName"列，从而不会丢失数据。谢谢，我理解，但是在这种情况下，名称列中的所有内容始终都是两个单词，因此这不应该是一个问题。当一个名字只有一个单词时，这会将名字和姓氏设置为相同的事物

对于要处理全名的人：John->名：John，姓：null

1234567SELECT if( INSTR(`name`, ' ')=0, TRIM(SUBSTRING(`name`, INSTR(`name`, ' ')+1)), TRIM(SUBSTRING(`name`, 1, INSTR(`name`, ' ')-1)) ) first_name, if( INSTR(`name`, ' ')=0, null, TRIM(SUBSTRING(`name`, INSTR(`name`, ' ')+1)) ) last_name

John Doe可以正常工作。但是，如果用户只填写John而没有姓氏，则SUBSTRING(name, INSTR(name, ' ')+1)) as lastname将返回John而不是null，而firstname将以SUBSTRING(name, 1, INSTR(name, ' ')-1)返回null。

就我而言，我添加了条件检查以正确确定姓氏并进行修剪以防止它们之间出现多个空格。

SUBSTRING_INDEX在SQL 2018中不适用于我，因此我使用了以下方法：

12345declare @fullName varchar(50) = 'First Last1 Last2' declare @first varchar(50) declare @last varchar(50) select @last = right(@fullName, len(@fullName)-charindex(' ',@fullName, 1)), @first = left(@fullName, (charindex(' ', @fullName, 1))-1);

产出@first ='First'，@last ='Last1 Last2'

只是想分享我的解决方案。它也适用于中间名。中间名将添加到名字中。

123SELECT TRIM(SUBSTRING(name,1, LENGTH(name)- LENGTH(SUBSTRING_INDEX(name, ' ', -1)))) AS firstname, SUBSTRING_INDEX(name, ' ', -1) AS lastname 相关讨论不知道为什么这被否决了。它工作得很好，并且如上所述，将中间名添加到名字中，并保留姓氏作为姓氏。如果这是期望的行为，那么这将完美地回答它。

从google到这里，想出了一个略有不同的解决方案，该解决方案确实可以处理包含两个以上部分的名称(最多5个名称部分，这将由空格字符创建)。这会将" last_name"列设置为" first name"(第一个空格)右侧的所有内容，还将" full_name"设置为" first name"部分。也许在运行此:-)之前备份您的数据库，但这对我有用：

12345678910111213UPDATE users SET name_last = \t CASE WHEN LENGTH(SUBSTRING_INDEX(full_name, ' ', 1)) = LENGTH(full_name) THEN '' WHEN LENGTH(SUBSTRING_INDEX(full_name, ' ', 2)) = LENGTH(full_name) THEN SUBSTRING_INDEX(del_name, ' ', -1) WHEN LENGTH(SUBSTRING_INDEX(full_name, ' ', 3)) = LENGTH(full_name) THEN SUBSTRING_INDEX(del_name, ' ', -2) WHEN LENGTH(SUBSTRING_INDEX(full_name, ' ', 4)) = LENGTH(full_name) THEN SUBSTRING_INDEX(del_name, ' ', -3) WHEN LENGTH(SUBSTRING_INDEX(full_name, ' ', 5)) = LENGTH(full_name) THEN SUBSTRING_INDEX(del_name, ' ', -4) WHEN LENGTH(SUBSTRING_INDEX(full_name, ' ', 6)) = LENGTH(full_name) THEN SUBSTRING_INDEX(del_name, ' ', -5) ELSE '' END, full_name = SUBSTRING_INDEX(full_name, ' ', 1) WHERE LENGTH(name_last) = 0 or LENGTH(name_last) is null or name_last = ''

我有一个类似的问题，但是名称包含多个名称，例如。" FirstName MiddleNames LastName"，它应该是" MiddleNames"而不是" MiddleName"。

所以我用substring()和reverse()的组合来解决我的问题：

12345select SystemUser.Email, SystemUser.Name, Substring(SystemUser.Name, 1, instr(SystemUser.Name, ' ')) as 'First Name', reverse(Substring(reverse(SystemUser.Name), 1, instr(reverse(SystemUser.Name), ' '))) as 'Last Name',

我不需要" MiddleNames"部分，也许这不是解决它的最有效方法，但对我有用。

【本文地址】

公司简介

联系我们