Creating SELECT Statement to Filter Names from a Single Column

Question

We have a column of names and there is multiple things that are needing to be parsed as it has to come out as LASTNAME FIRSTNAME MIDDLENAME. The column always starts with LASTNAME then comma-separated to FIRSTNAME then it can be comma-separated or space-separated or even bracket-separated (()) for MIDDLENAME.

I have code to filter the LASTNAME:

SUBSTRING([Name], 0, PATINDEX('%,%', [Name])) AS LastName

But I am having trouble filtering through the FIRSTNAME and MIDDLENAME. Here are some examples of what a column looks like

LASTNAME,FIRSTNAME MIDDLENAME
LASTNAME,FIRSTNAME,MIDDLENAME
LAST NAME,FIRSTNAME MIDDLENAME
LAST NAME,FIRSTNAME (MIDDLENAME)

Now if there is something in brackets, we don't want to display that information in the brackets. so we would ignore the MIDDLENAME. The column can have any combination for the LASTNAME, FIRSTNAME, and MIDDLENAME with spaces or commas or brackets and the MIDDLENAME can exist outside the brackets as well:

LASTNAME,FIRSTNAME (NICKNAME) MIDDLENAME

In that case we want the MIDDLENAME and to ignore the NICKNAME

Any help is greatly appreciated!

Can you use dbfiddle.uk or sqlfiddle.com to create a demo schema and data? And what version of SQL? — Shawn, Apr 16 '19 at 18:16
How are you going to deal with first names that can have spaces in them? — pmbAustin, Apr 16 '19 at 18:17
Names are always a crap shoot but there are many functions out there such as [ParseName](https://learn.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql?view=sql-server-2017) and [This Solution look promising too](https://stackoverflow.com/a/159760/100283) [@res74](https://stackoverflow.com/users/2872221/res74) — Mark Kram, Apr 16 '19 at 18:19

Ali Al-Mosawi · Accepted Answer · 2019-04-17T16:23:34.093

I will add your examples in a memory table so we can test them as below:- (you dont need this)

Declare @NameTable table (fullname varchar(250))
insert into @NameTable values
('LASTNAME,FIRSTNAME MIDDLENAME'),
('LASTNAME,FIRSTNAME,MIDDLENAME'),
('LAST NAME,FIRSTNAME MIDDLENAME'),
('LAST NAME,FIRSTNAME (MIDDLENAME)'),
('LASTNAME,FIRSTNAME (NICKNAME) MIDDLENAME'),
('LASTNAME,FIRSTNAME') -- no middle name

Lets try to break it to some smaller steps, first get the brackets out, and then the last name and continue till we get all the parts.

;with FindBrackts as (
/* get rid of brackets */
select *,CHARINDEX('(',fullname) bStart,CHARINDEX(')',fullname) bEnd from @NameTable
),RemoveBrackts as (
    select case when bStart>0 then substring(fullname,1,bStart-1)+substring(fullname,bEnd+2,len(fullname)-bEnd+1)
        else fullname end fullname from FindBrackts)
,LastNameAndTheRest as (
    select substring(fullname,1,CHARINDEX(',',fullname)-1) [LASTNAME]
,substring(fullname,CHARINDEX(',',fullname)+1,len(fullname)-CHARINDEX(',',fullname)) [TheRest] from RemoveBrackts
),LastFirstMiddle as (
    select [LASTNAME],[TheRest],CHARINDEX(',',replace([TheRest],' ',',')) [mStart] from LastNameAndTheRest
)
select [LASTNAME]
    ,case when mStart=0 then [TheRest] else substring([TheRest],1,[mStart]-1) end FIRSTNAME  
    ,case when mStart=0 then null else substring([TheRest],[mStart]+1,len([TheRest])-[mStart]) end MIDDLENAME 
    from LastFirstMiddle

The result will be something like the below:-

LASTNAME    FIRSTNAME   MIDDLENAME
========    =========   ==========
LASTNAME    FIRSTNAME   MIDDLENAME
LASTNAME    FIRSTNAME   MIDDLENAME
LAST NAME   FIRSTNAME   MIDDLENAME
LAST NAME   FIRSTNAME   NULL
LASTNAME    FIRSTNAME   MIDDLENAME

If this helps and you find out there are more conditions please add them and may update the query to take them in consideration.

here is the same on SQL Fiddle http://sqlfiddle.com/#!18/2dd42/2/0

Or if you dont want any CTE, its a bit long but you can try the below:-

select 
    SUBSTRING(fullname, 0, CHARINDEX(',', fullname)) AS LastName 
    ,SUBSTRING(replace(SUBSTRING(fullname,CHARINDEX(',', fullname)+1,len(fullname)-CHARINDEX(',', fullname)),' ',','),1,CHARINDEX(',',replace(SUBSTRING(fullname,CHARINDEX(',', fullname)+1,len(fullname)-CHARINDEX(',', fullname)),' ',','))-1) [FirstName]
    ,SUBSTRING(fullname,2+len(fullname)-CHARINDEX(',',reverse(replace(fullname,' ',','))),len(fullname)) MiddleName
    from @NameTable

Results will be as a below, (a bit different than the previous solution)

LastName    FirstName   MiddleName
========    =========   ==========
LASTNAME    FIRSTNAME   MIDDLENAME
LASTNAME    FIRSTNAME   MIDDLENAME
LAST NAME   FIRSTNAME   MIDDLENAME
LAST NAME   FIRSTNAME   (MIDDLENAME)
LASTNAME    FIRSTNAME   MIDDLENAME

if there is no Middlename , the CTE will handle it, but the select will need to be updated to the below:-

select 
    SUBSTRING(fullname, 0, CHARINDEX(',', fullname)) AS LastName 
    ,case when CHARINDEX(',',replace(SUBSTRING(fullname,CHARINDEX(',', fullname)+1,len(fullname)-CHARINDEX(',', fullname)),' ',','))>0 then
        SUBSTRING(replace(SUBSTRING(fullname,CHARINDEX(',', fullname)+1,len(fullname)-CHARINDEX(',', fullname)),' ',','),1,CHARINDEX(',',replace(SUBSTRING(fullname,CHARINDEX(',', fullname)+1,len(fullname)-CHARINDEX(',', fullname)),' ',','))-1) 
        else replace(SUBSTRING(fullname,CHARINDEX(',', fullname)+1,len(fullname)-CHARINDEX(',', fullname)),' ',',') end [FirstName]
    ,case when CHARINDEX(',',replace(SUBSTRING(fullname,CHARINDEX(',', fullname)+1,len(fullname)-CHARINDEX(',', fullname)),' ',','))>0 then
        SUBSTRING(fullname,2+len(fullname)-CHARINDEX(',',reverse(replace(fullname,' ',','))),len(fullname))
        else  NULL end MiddleName
    from @NameTable

This is a bit long and may be it could be written shorter. anyhow the results would be as below:-

LastName    FirstName   MiddleName
=========   =========   ==========
LASTNAME    FIRSTNAME   MIDDLENAME
LASTNAME    FIRSTNAME   MIDDLENAME
LAST NAME   FIRSTNAME   MIDDLENAME
LAST NAME   FIRSTNAME   (MIDDLENAME)
LASTNAME    FIRSTNAME   MIDDLENAME
LASTNAME    FIRSTNAME   NULL

Thank you very much Ali. It's on the right track that's for sure, but in my information I failed to mention that sometimes there is no MIDDLENAME. That is completely my stupidity and I apologize for that. Any chance your SELECT can take that into account also? — res74, Apr 16 '19 at 21:52
It's amazingly close, but I just need it to ignore if the MIDDLENAME is in brackets as we don't want to display anything in the brackets. I'm also noticing that if there is 2 MIDDLENAMEs it only takes the last one. Any chance I can ask you to update it to display the MIDDLENAMEs if there are 2? Example: ABDEL WAHAB,HODA GALAL HASSAN — res74, Apr 17 '19 at 22:26
The first select manage that, check it out, the one that starts with ;with FindBrackts as .....,, it remove the middlename if there were brackets and will show the middle name even if its more than one part. — Ali Al-Mosawi, Apr 17 '19 at 23:06

score 0 · Answer 2 · answered Apr 17 '19 at 01:32

Please try this:

;WITH Data AS (
    SELECT a.ID,a.LastName
        ,REPLACE(REPLACE(REPLACE(REPLACE(a.RotN,' (','.'),')',''),',','.'),' ','.') AS [DotSplitName]
    FROM (
        SELECT ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS [ID]
            ,SUBSTRING(t.fullname,1,CHARINDEX(',',t.fullname)-1) AS [LastName]
            ,SUBSTRING(t.fullname,CHARINDEX(',',t.fullname)+1,999) AS [RotN] /*Rest of the name*/
        FROM @NameTable t
        WHERE t.fullname LIKE '%[,]%'
    ) a
)
SELECT d.ID,d.LastName
    ,COALESCE(PARSENAME(d.DotSplitName,3),PARSENAME(d.DotSplitName,2)) AS [FirstName]
    ,PARSENAME(d.DotSplitName,1) AS [MiddleName]
    ,CASE WHEN PARSENAME(d.DotSplitName,3) IS NOT NULL THEN PARSENAME(d.DotSplitName,2) ELSE NULL END AS [NickName]
FROM Data d
;

Thank you Ali Al-Mosawi for Memory table example

Creating SELECT Statement to Filter Names from a Single Column

2 Answers2