如何将单列值拆分为多列值？

Question

我有一个问题，需要将单列值拆分为多列值。

例如：

Name
------------
abcd efgh
ijk lmn opq
asd j. asdjja
asb (asdfas) asd
asd

而我需要输出结果就像这样：

first_name             last_name
----------------------------------
abcd                     efgh
ijk                      opq
asd                      asdjja
asb                      asd
asd                      null

中间名可省略（无需中间名）。列已创建，需要将数据插入该单个 Name 列。

- Shahsra

---

这是您想在SQL Server中完成的任务吗？在SELECT语句中？在INSERT或UPDATE语句中？提供更多细节将有助于我们回答您的问题。 - BinaryTox1n

这是一个糟糕的名称存储设计，你应该考虑将名字、姓氏和中间名作为单独的列。我希望你不要从这个表格中生成任何拆分名称的报告，就像你所请求的那样。 - Kris Ivanov

需要在一个选择语句中。实际上，这是用于存储过程，该存储过程通过从表中选择值来插入数据。所以，如果我可以在选择语句中得到它，那将是很好的... - Shahsra

Answer 1

你的方法不能正确处理很多名称，但是...

SELECT CASE
         WHEN name LIKE '% %' THEN LEFT(name, Charindex(' ', name) - 1)
         ELSE name
       END,
       CASE
         WHEN name LIKE '% %' THEN RIGHT(name, Charindex(' ', Reverse(name)) - 1)
       END
FROM   YourTable 

- Martin Smith

---

我遇到了错误：#1305 - 函数preprodshem.Charindex不存在。经过一些搜索，我发现mysql不支持这个函数，需要使用SUBSTRING_INDEX进行一些更改来解决这个问题。 - Mimouni

@IlyasMimouni - 这个问题的标签是SQL Server。 - Martin Smith

Answer 2

马丁方法的替代方案

select LEFT(name, CHARINDEX(' ', name + ' ') -1),
       STUFF(name, 1, Len(Name) +1- CHARINDEX(' ',Reverse(name)), '')
from somenames

示例表格

create table somenames (Name varchar(100))
insert somenames select 'abcd efgh'
insert somenames select 'ijk lmn opq'
insert somenames select 'asd j. asdjja'
insert somenames select 'asb (asdfas) asd'
insert somenames select 'asd'
insert somenames select ''
insert somenames select null

- RichardTheKiwi

---

@Shahsra - 是的。按照定义，SQL是一种基于集合的语言，因此您在一个记录上执行的操作会同样地应用于所有记录。 - RichardTheKiwi

目前看起来只是简单地重复了名字？ - Martin Smith

@Martin - 谢谢你发现了这个问题。现在已经修复了，不再是短的了 :( - RichardTheKiwi

@RichardTheKiwi 如果我想把名字和中间名作为名字，同时将姓氏分开，我该怎么办？谢谢。 - Mimi

Answer 3

;WITH Split_Names (Name, xmlname)
AS
(
    SELECT 
    Name,
    CONVERT(XML,'<Names><name>'  
    + REPLACE(Name,' ', '</name><name>') + '</name></Names>') AS xmlname
      FROM somenames
)

 SELECT       
 xmlname.value('/Names[1]/name[1]','varchar(100)') AS first_name,    
 xmlname.value('/Names[1]/name[2]','varchar(100)') AS last_name
 FROM Split_Names

请参考以下链接了解更多关于将分隔字符串转换为值的方法：

http://jahaines.blogspot.in/2009/06/converting-delimited-string-of-values.html

- bvr

Answer 4

你需要的是一个分割用户定义函数。使用它，解决方案看起来像这样：

With SplitValues As
    (
    Select T.Name, Z.Position, Z.Value
        , Row_Number() Over ( Partition By T.Name Order By Z.Position ) As Num
    From Table As T
        Cross Apply dbo.udf_Split( T.Name, ' ' ) As Z
    )
Select Name
    , FirstName.Value
    , Case When ThirdName Is Null Then SecondName Else ThirdName End As LastName
From SplitValues As FirstName
    Left Join SplitValues As SecondName
        On S2.Name = S1.Name
            And S2.Num = 2
    Left Join SplitValues As ThirdName
        On S2.Name = S1.Name
            And S2.Num = 3
Where FirstName.Num = 1

这里是一个样例分割函数：

Create Function [dbo].[udf_Split]
(   
    @DelimitedList nvarchar(max)
    , @Delimiter nvarchar(2) = ','
)
RETURNS TABLE 
AS
RETURN 
    (
    With CorrectedList As
        (
        Select Case When Left(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            + @DelimitedList
            + Case When Right(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
            As List
            , Len(@Delimiter) As DelimiterLen
        )
        , Numbers As 
        (
        Select TOP( Coalesce(DataLength(@DelimitedList)/2,0) ) Row_Number() Over ( Order By c1.object_id ) As Value
        From sys.columns As c1
            Cross Join sys.columns As c2
        )
    Select CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen As Position
        , Substring (
                    CL.List
                    , CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen     
                    , CharIndex(@Delimiter, CL.list, N.Value + 1)                           
                        - ( CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen ) 
                    ) As Value
    From CorrectedList As CL
        Cross Join Numbers As N
    Where N.Value <= DataLength(CL.List) / 2
        And Substring(CL.List, N.Value, CL.DelimiterLen) = @Delimiter
    )

- Thomas

---

这会将内容拆分成行，而不是列。 - Nick.McDermaid

Answer 5

我最近使用了它：

select 
substring(name,1,charindex(' ',name)-1) as Col1,
substring(name,charindex(' ',name)+1,len(name)) as Col2 
from TableName

- Vaibhav Rai

Answer 6

SELECT
   SUBSTRING_INDEX(SUBSTRING_INDEX(rent, ' ', 1), ' ', -1) AS currency,
   SUBSTRING_INDEX(SUBSTRING_INDEX(rent, ' ', 3), ' ', -1) AS rent
FROM tolets

- Md Shahadat Hossain

---

那是MySQL，不是MS SQL。 - AjV Jsy

Answer 7

以下是我在SQLite数据库上的操作方法：

SELECT SUBSTR(name, 1,INSTR(name, " ")-1) as Firstname,
SUBSTR(name, INSTR(name," ")+1, LENGTH(name)) as Lastname
FROM YourTable;

希望能有所帮助。

- Bokai