如何将一个列值分割为多个列值?

I have a problem splitting single column values to multiple column values.

我有一个问题，将单个列值分割为多个列值。

For Example:

例如:

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

and I need the output something like this:

我需要这样的输出:

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

The middle name can be omitted (no need for a middle name) The columns are already created and need to insert the data from that single Name column.

可以省略中间名(不需要中间名)列已经创建，并且需要从该名称列插入数据。

6 个解决方案

#1

Your approach won't deal with lot of names correctly but...

你的方法不会正确处理很多名字，但是…

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

#2

An alternative to Martin's

马丁的替代品

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

Sample table

样表

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

#3

What you need is a split user-defined function. With that, the solution looks like

您需要的是一个分离的用户定义函数。有了它，解看起来是这样的

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

Here's a sample split function:

下面是一个分割函数的示例:

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
    )

#4

;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

and also check the link below for reference

并检查下面的链接以供参考。

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

#5

Here is how I did this on a SQLite database:

下面是我在SQLite数据库上做的:

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

从表中选择SUBSTR(name, 1,INSTR(name， ")-1)作为Firstname, SUBSTR(name, INSTR(name，" ")+1, LENGTH(name)作为Lastname;

Hope it helps.

希望它可以帮助。

#6

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

#1