SQL Script to parse the FullName field to individual components- part 1

Often DBA’s get requests to identify count of unique firstnames from a name column. Here is one such request that I received. Here is the situation:

I have a column named name and it has the names in the below format with first and last name together

Sachin Tendulkar
Mahender S Dhoni
Suresh, Raina
Rahul ,Dravid

and i want to select only first names which are below and in the below format where I don’t need comma’s and also I don’t need trimmed firstname:


Once this is done I want a count of unique firstnames only. Please find the code below for such scenario:

Select Fname, Count(*) as cnt from (
SELECT case when charindex(',', Name) > 0
 Then SUBSTRING(Name, 1, charindex(',', Name) -1)
 SUBSTRING(Name, 1, CHARINDEX(' ', name, -1))
 end as Fname

FROM [table] dp (nolock)
 inner join <join another table if required on common columns>
 where <specify any specific conditions>') T
GROUP BY Fname order by cnt desc

Hope this helps!

This entry was posted in SQL Server DBA Stuff and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s