How to split a single column values to multiple column values

Question

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

User · Answer

WITH Split Names  Name  xmlname  AS       SELECT      Name      CONVERT XML   lt Names gt  lt name gt           REPLACE Name        lt  name gt  lt name gt        lt  name gt  lt  Names gt    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

User · Answer

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    lt  gt   Delimiter Then  Delimiter Else    End                DelimitedList               Case When Right  DelimitedList  Len  Delimiter    lt  gt   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  lt   DataLength CL List    2         And Substring CL List  N Value  CL DelimiterLen     Delimiter

User · Answer

Here is how I did this on a SQLite database   SELECT SUBSTR name  1 INSTR name      -1  as Firstname  SUBSTR name  INSTR name      1  LENGTH name   as Lastname FROM YourTable    Hope it helps

User · Answer

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

User · Answer

I used it recently  select  substring name 1 charindex     name -1  as Col1  substring name charindex     name  1 len name   as Col2  from TableName

User · Answer

SELECT    SUBSTRING INDEX SUBSTRING INDEX rent       1        -1  AS currency     SUBSTRING INDEX SUBSTRING INDEX rent       3        -1  AS rent FROM tolets

User · Answer

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

[sql] How to split a single column values to multiple column values?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to string