Database Simplified Headline Animator

Database Simplified

Saturday 10 September 2011

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

   

9 comments:

  1. GREAT KULDEEP.... U R GENIUS....

    ReplyDelete
  2. I was able to use your query solution, and it saved me quite a bit of time. Thank you!

    ReplyDelete
  3. Not working for me. If middle name doesnt exist, i see the last name in MiddleName column. Please help.

    ReplyDelete
  4. It is working fine can you please tell how to remove Mr./Ms./Sh. from First Name and split in three parts

    ReplyDelete
  5. Brilliant, you saved my bacon

    ReplyDelete
  6. Hi Kuldeep,
    I want 'Kuldeep Singh' as FirstName & 'Bisht' as LastName
    Could you please suggest...

    Regards,
    Mohammed

    ReplyDelete
  7. How would solution #1 work with Update Set?

    ReplyDelete
  8. Working like a champ

    ReplyDelete