Split Name Into First, Middle and Last Name In Sql Server

Last Week one of my Friend ask "how to split name into first middle and last name.” Over this he also said he tried it but its very confusing and complex So I Thought of writing a blog on it.

Create Following Table For Sample

Create Table Employee (EmpId int Identity(1,1),EmpName Varchar(200))

Go

Insert Into Employee (EmpName)

Values('Kuldeep Singh Bisht'),('Sandeep Verma'),('Manisha')

Go

Select * From Employee

EmpId	EmpName
1	Kuldeep Singh Bisht
2	Sandeep Verma
3	Manisha

So Now We Need a  Query Which Can Split EmpName Into FirstName, LastName and MiddleName.

Soluton 1 :

Split Name In Query

Select Ltrim(SubString(EmpName,1,Isnull(Nullif(CHARINDEX(' ',EmpName),0),1000))) As FirstName,
Ltrim(SUBSTRING(EmpName,CharIndex(' ',EmpName),
CAse When (CHARINDEX(' ',EmpName,CHARINDEX(' ',EmpName)+1)-CHARINDEX(' ',EmpName))<=0 then 0 
else CHARINDEX(' ',EmpName,CHARINDEX(' ',EmpName)+1)-CHARINDEX(' ',EmpName) end )) as MiddleName,
Ltrim(SUBSTRING(EmpName,Isnull(Nullif(CHARINDEX(' ',EmpName,Charindex(' ',EmpName)+1),0),CHARINDEX(' ',EmpName)),
Case when Charindex(' ',EmpName)=0 then 0 else LEN(EmpName) end)) as LastName
From Employee

Go

Output:

EmpId	EmpName	FirstName	MiddleName	LastName
1	Kuldeep Singh Bisht	Kuldeep	Singh	Bisht
2	Sandeep Verma	Sandeep		Verma
3	Manisha	Manisha

 

Solution 2 :

You Create Computed Column In your table So that whenever EmpName Field Is Updated or Inserted FirstName,MiddleName and LastName Fields Are Calculated Automatically. See Followng How ?

Create Following Table

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](120) NULL,
    [FirstName]  AS 
                (CONVERT([varchar](60),ltrim(substring([Name],(1),
                isnull(nullif(charindex(' ',[Name]),(0)),(1000)))),0)),
    [MiddleName]  AS 
                (CONVERT([varchar](60),ltrim(substring([Name],
                charindex(' ',[Name]),case when (charindex(' ',
                [Name],charindex(' ',[Name])+(1))-charindex(' ',[Name]))<=(0) 
                then (0) else charindex(' ',[Name],charindex(' ',
                [Name])+(1))-charindex(' ',[Name]) end)),0)),
    [LastName]  AS 
                (CONVERT([varchar](60),ltrim(substring([Name],
                isnull(nullif(charindex(' ',[Name],charindex(' ',[Name])+(1)),(0)),
                charindex(' ',[Name])),case when charindex(' ',[Name])=(0) 
                then (0) else len([Name]) end)),0)),
)

Go

Note Above FirstName, MiddleName and LastName Fields as Computed Column.

Now Try Inserting Records Into Table

Insert into Employee(Name)
Values('Kuldeep Singh Bisht'),('Sandeep Verma'),('Manisha')
Go

Now See The Result.

EmpId	EmpName	FirstName	MiddleName	LastName
1	Kuldeep Singh Bisht	Kuldeep	Singh	Bisht
2	Sandeep Verma	Sandeep		Verma
3	Manisha	Manisha