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.

Is this something you want to do within SQL Server? In a `SELECT` statement? In an `INSERT` or `UPDATE` statement? More details would help us answer your question — BinaryTox1n, Feb 25 '11 at 22:58
that is terrible design for name storage, you should consider having first, last, middle name as separate columns, I hope you don't do any reports off this table that splits names as you requested — Kris Ivanov, Feb 25 '11 at 22:58
Need that in a select statement. Actually it is for stored procedure which is inserting the data by selecting values from a table.So I get it in select statement that will be great... — Shahsra, Feb 25 '11 at 23:00

score 23 · Accepted Answer · answered Feb 25 '11 at 23:05

23

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

answered Feb 25 '11 at 23:05

Martin Smith

438,706
87
741
845

I get the error : #1305 - FUNCTION preprodshem.Charindex does not exist. after some search I found that this function is not supported by mysql, and need some changes using : SUBSTRING_INDEX to resolve this. – Mimouni Sep 21 '16 at 10:09
3

@IlyasMimouni - this question is tagged SQL Server. – Martin Smith Sep 21 '16 at 10:41

RichardTheKiwi · Answer 2 · 2011-02-25T23:29:03.070

16

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

edited Feb 25 '11 at 23:29

answered Feb 25 '11 at 23:10

RichardTheKiwi

105,798
26
196
262

1

@Shahsra - Yes. By definition SQL is a SET-based language so what you do on one record is done on all records equally.. – RichardTheKiwi Feb 25 '11 at 23:18
This appears to just give the forename twice at the moment? – Martin Smith Feb 25 '11 at 23:21
1

@Martin - thanks for catching it. Fixed now. No longer short :( – RichardTheKiwi Feb 25 '11 at 23:30
@RichardTheKiwi What should I do if I want the first name and middle name together as first name and last name seperaratedly? thanks. – Mimi Sep 16 '15 at 09:01

score 3 · Answer 3 · answered Feb 27 '13 at 09:23

;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

score 2 · Answer 4 · answered Feb 25 '11 at 23:02

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
    )

This splits into rows, not columns – Nick.Mc Aug 10 '21 at 12:39 — Nick.Mc, Aug 10 '21 at 12:39

score 2 · Answer 5 · answered Jul 29 '20 at 05:37

2

I used it recently:

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

answered Jul 29 '20 at 05:37

Vaibhav Rai

21
1

score 1 · Answer 6 · edited Mar 12 '17 at 08:15

1

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

edited Mar 12 '17 at 08:15

Fruchtzwerg

10,999
12
40
49

answered Mar 12 '17 at 05:54

Md Shahadat Hossain

339
2
13

That's MySQL, not MS SQL – AjV Jsy Apr 11 '19 at 09:39

score 0 · Answer 7 · edited Jul 30 '19 at 05:10

0

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.

edited Jul 30 '19 at 05:10

Erik Philips

53,428
11
128
150

answered Oct 21 '16 at 06:50

Bokai

107
3
17

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

7 Answers7

Linked

Related