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
Mahender S Dhoni
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) else 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!