在SQL Server中将一列拆分成多个列

Question

我有一张名为Employee的表格，包含以下列：

FullName
--------------------
Holmes, John Henry

我想修改表格，将FullName更改为LastName、FirstName和MiddleName。我应该使用哪些SQL命令来实现这一点？我希望得到的结果是:

LastName    FirstName    MiddleName
-----------------------------------
Holmes      John         Henry

我尝试过观看YouTube视频、查找论坛和阅读文章，但都没有成功。

由于我刚开始学习SQL，所以非常需要帮助。

- user3289917

---

你将如何知道，“Mary Jane”是一个名字还是一个名字加中间名？ - Matt Gibson

有人需要审核你的数据……否则你需要一些东西来减轻痛苦……你有什么数据库管理系统来确定你的字符串函数？ - Ctznkane525

@MattGibson 你好，为了方便假设，fullname 总是只有三个单词，lastname 总是第一个单词（后跟逗号和空格），firstname 是第二个单词（后跟空格），middlename 总是第三个单词。 - user3289917

你在使用哪个关系型数据库管理系统？ - Jay Gupta

@user3289917 这个假设很不错，但在现实世界的数据中极不可能发生。完成此操作的常规方法是将新列添加到表中并填充空数据，尽力进行批量加载，但还需要实际的人员审核和更正朴素数据转换。这有点糟糕，特别是如果您有成千上万个名称...这就是为什么第一次就要做对它非常重要的原因。 - Joel Coehoorn

Answer 1

假设您正在使用SQL Server（如标题中所提到的），您可以使用以下查询将全名拆分为姓、名和中间名：

select substring(FullName, 1, patindex('%,%', FullName) - 1) as LastName,
substring(
  FullName, 
  charindex(', ', FullName) + 2, 
  charindex(' ', FullName, charindex(', ', FullName) + 2) - charindex(', ', FullName) - 2) as FirstName,
substring(FullName, charindex(' ', FullName, charindex(', ', FullName) + 2) + 1, charindex(' ', FullName, -1)) as MiddleName
from Employee;

这将输出：

LastName  |  FirstName  |  MiddleName
-------------------------------------
  Holmes  |       John  |       Henry

请在以下网址尝试： http://sqlfiddle.com/#!18/e78fc/1 [编辑]
若要更新现有表格，以便将全名保存为姓、名和中间名，请执行以下操作：

(1) 通过添加FirstName、MiddleName和LastName列来更改表格：

alter table Employee 
add FirstName varchar(50), 
    MiddleName varchar(50),
    LastName varchar(50);

(2) 将全名分为姓、名和中间名，更新表格：

update Employee
set FirstName = substring(
    FullName, 
    charindex(', ', FullName) + 2, 
    charindex(' ', FullName, charindex(', ', FullName) + 2) - charindex(', ', FullName) - 2),
  MiddleName = substring(FullName, charindex(' ', FullName, charindex(', ', FullName) + 2) + 1, charindex(' ', FullName, -1)),
  LastName = substring(FullName, 1, patindex('%,%', FullName) - 1);

(3) 删除 FullName 列：

alter table Employee drop column FullName;

您可以在sqlfiddle上尝试以上SQL语句：http://sqlfiddle.com/#!18/a418d/2。

- pgngp

---

你好，这看起来很不错！但是我该如何永久地将FullName替换为LastName、FirstName和MiddleName呢？我想修改/更改FullName列，并将其变成Last、First和MiddleName，同时也要修改其中的值，而不仅仅是查看它们。 - user3289917

我已经更新了我的答案来解决这些问题。请查看[编辑]下面的部分。 - pgngp

Answer 2

首先，创建一个函数 像下面的例子一样。

    Create Or Alter Function
    [dbo].[Fn_GetItemArray]
(
        @StringValue Nvarchar(Max),
        @Index Int,
        @CharSplit Nvarchar(10)
)
Returns Nvarchar(Max)
As
Begin
Return
(
    Select
            s.[value]
        From
        (
            Select
                    ROW_NUMBER() Over (Order By (Select Null)) RowNumber,
                    s.[value]
                From STRING_SPLIT(@StringValue,@CharSplit) As s
        ) As s
        Where   s.[RowNumber] = @Index
)
End

然后按照以下方式使用。

Declare @TbTmp As Table ( FullName Nvarchar(255))

Insert Into
    @TbTmp
    Values
        ('Holmes John Henry'),
        ('Daniyal Foroutan Rad'),
        ('Ali One Emam')

Select 
        *
    From @TbTmp As s;

Select 
        [dbo].[Fn_GetItemArray](s.[FullName], 1, ' ') LastName,
        [dbo].[Fn_GetItemArray](s.[FullName], 2, ' ') LastName,
        [dbo].[Fn_GetItemArray](s.[FullName], 3, ' ') LastName
    From @TbTmp As s;

当然，在这个模型中，分隔符必须是相同的。 您可以修改函数以适应您的输入。 示例图片 祝您好运。

- daniyal_1363