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

How to split a single column values to multiple column values?

提问by Shahsra

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 Namecolumn.

中间名可以省略（不需要中间名） 列已经创建，需要从该单列插入数据Name。

采纳答案by Martin Smith

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 

回答by RichardTheKiwi

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

回答by bvr

;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

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

回答by Thomas

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
    )

回答by Bokai

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;

Hope it helps.

希望能帮助到你。

回答by Md Shahadat Hossain

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