Split one column into multiple columns in SQL Server

Question

I have a table called Employee which has the following column:

FullName
--------------------
Holmes, John Henry

I want to alter the table and turn FullName into LastName, FirstName and MiddleName. What SQL commands should I use to make this happen? The result I wish to have is this:

LastName    FirstName    MiddleName
-----------------------------------
Holmes      John         Henry

I have tried youtube videos, forums and articles but to no avail.

Any help would be greatly appreciated as I am just beginning with SQL.

How are you going to know if, say, "Mary Jane" is a first name or a first name plus a middle name? — Matt Gibson
– Matt Gibson, Commented Mar 24, 2018 at 17:09
someone will have to audit your data...otherwise youll need something to kill the pain...what dbms do you have to determine your string functions? — Ctznkane525
– Ctznkane525, Commented Mar 24, 2018 at 17:24
@MattGibson hello, for the sake of assumption, fullname will always have only three words, with lastname always being the first word (followed by a comma and space), firstname being the second word (followed by a space) and middlename always being the third word. — user3289917
– user3289917, Commented Mar 24, 2018 at 17:31
@user3289917 That's a nice assumption that is extremely unlikely to ever play out in real world data. The normal way to accomplish this is to add the new columns to the table with empty data, bulk load it as best you can, but then also have real live humans review records to audit and correct the naive data conversion. That kinda sucks, especially if you have many thousands of a names... which is why it's so important to get this right the first time. — Joel Coehoorn
– Joel Coehoorn, Commented Mar 25, 2018 at 19:23

pgngp · Accepted Answer · 2018-03-25 19:14:51Z

6

Assuming you are using SQL Server (as mentioned in the title), you can use the following query to split the full name into last, first, and middle:

select substring(FullName, 1, patindex('%,%', FullName) - 1) as LastName,
substring(
  FullName, 
  charindex(', ', FullName) + 2, 
  charindex(' ', FullName, charindex(', ', FullName) + 2) - charindex(', ', FullName) - 2) as FirstName,
substring(FullName, charindex(' ', FullName, charindex(', ', FullName) + 2) + 1, charindex(' ', FullName, -1)) as MiddleName
from Employee;

This will output:

LastName  |  FirstName  |  MiddleName
-------------------------------------
  Holmes  |       John  |       Henry

Try it out at: http://sqlfiddle.com/#!18/e78fc/1

[EDIT]
In order to update your existing table such that full name is saved as last, first, and middle, you need to:

(1) Alter the table by adding FirstName, MiddleName, and LastName columns:

alter table Employee 
add FirstName varchar(50), 
    MiddleName varchar(50),
    LastName varchar(50);

(2) Update the table by breaking the full name into last, first, and middle:

update Employee
set FirstName = substring(
    FullName, 
    charindex(', ', FullName) + 2, 
    charindex(' ', FullName, charindex(', ', FullName) + 2) - charindex(', ', FullName) - 2),
  MiddleName = substring(FullName, charindex(' ', FullName, charindex(', ', FullName) + 2) + 1, charindex(' ', FullName, -1)),
  LastName = substring(FullName, 1, patindex('%,%', FullName) - 1);

(3) Drop the FullName column:

alter table Employee drop column FullName;

You can try the above sql at sqlfiddle: http://sqlfiddle.com/#!18/a418d/2

edited Mar 25, 2018 at 19:14

answered Mar 24, 2018 at 18:55

pgngp

1,5805 gold badges16 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3289917 Over a year ago

Hello, this seems great! But how do I go about replacing FullName as LastName, FirstName, and MiddleName permanently? I want to alter/change the FullName column and make it into Last, First, and MiddleName, along with the values, not just view them.

pgngp Over a year ago

I've updated my answer to address these issues. Please see the section under [EDIT].

daniyal_1363 · Accepted Answer · 2023-08-03 07:10:09Z

First, create a function Like the example below.

    Create Or Alter Function
    [dbo].[Fn_GetItemArray]
(
        @StringValue Nvarchar(Max),
        @Index Int,
        @CharSplit Nvarchar(10)
)
Returns Nvarchar(Max)
As
Begin
Return
(
    Select
            s.[value]
        From
        (
            Select
                    ROW_NUMBER() Over (Order By (Select Null)) RowNumber,
                    s.[value]
                From STRING_SPLIT(@StringValue,@CharSplit) As s
        ) As s
        Where   s.[RowNumber] = @Index
)
End

Then use it as follows.

Declare @TbTmp As Table ( FullName Nvarchar(255))

Insert Into
    @TbTmp
    Values
        ('Holmes John Henry'),
        ('Daniyal Foroutan Rad'),
        ('Ali One Emam')

Select 
        *
    From @TbTmp As s;

Select 
        [dbo].[Fn_GetItemArray](s.[FullName], 1, ' ') LastName,
        [dbo].[Fn_GetItemArray](s.[FullName], 2, ' ') LastName,
        [dbo].[Fn_GetItemArray](s.[FullName], 3, ' ') LastName
    From @TbTmp As s;

Of course, in this model, the separator character must be the same. You can modify the function so that it fits your inputs.

Sample image

Good luck.

Collectives™ on Stack Overflow

Split one column into multiple columns in SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related