TSQL to Parse Name with comma

Posted on 2013-01-12
Last Modified: 2013-01-15
I've been trying to implement the solution to this post with the first delimiter being a comma, but I'm only getting two characters of the first name and no middle name.

The code I'm using is:
     LEFT(Instr1_Name, CHARINDEX(',', Instr1_Name + ' ') - 1) AS LName,
    RTRIM(SUBSTRING(Instr1_Name, CHARINDEX(',', Instr1_Name + ' ') + 1,
        CHARINDEX(' ', Instr1_Name + ' ', CHARINDEX(' ', Instr1_Name + ' ') + 1) -
        CHARINDEX(' ', Instr1_Name + ' '))) AS FName,
    SUBSTRING(Instr1_Name, CHARINDEX(' ', Instr1_Name + ' ',
        CHARINDEX(' ', Instr1_Name + ' ') + 1) + 1, LEN(Instr1_Name)) AS Mname
      INTO #TempFac FROM SemesterCoursesImport  
              LEFT JOIN [Faculty Info]  
              ON SemesterCoursesImport.Instr1_EmplID = [Faculty Info].EmplID  
              WHERE [Faculty Info].EmplID Is Null  
              AND SemesterCoursesImport.Instr1_EmplID Is Not Null  
              AND SemesterCoursesImport.SemesterPS = '2131'

The results I'm getting with the above statement are attached.  What do I need to do to change this statement to get the correct first and middle name?  The nested CharIndex commands are confusing.  I'll give extra points if you can explain in English how the substring and charindex are used to get the correct result. In particular, I don't understand why ' ' is added to the end of the Instr1_name.
Thanks, Susan
Question by:scover22
  • 2
LVL 22

Accepted Solution

Steve Wales earned 400 total points
ID: 38770936
I played with it a little and came up with the following (which works):

select Instr1_Name
      ,LEFT(Instr1_Name, CHARINDEX(',', Instr1_Name) - 1) AS LName
      ,SUBSTRING(Instr1_Name, CHARINDEX(',', Instr1_Name) + 1, CHARINDEX(' ', Instr1_Name) - CHARINDEX(',', Instr1_Name) - 1) AS FName
      ,SUBSTRING(Instr1_Name, CHARINDEX(' ', Instr1_Name) + 1, len(Instr1_Name)) as MName
FROM (select 'Smith,John C' as Instr1_Name) as a

Open in new window

It's different to the original query but returns the data you want.

Note that the above is fairly rigid in what it's doing and expects a name exactly in this format (it doesn't work if the input string isn't in that format):

Last,First Middle

That's no space after the comma and one space after the first name.  The script is very unforgiving for a variable format, but it's a starting point.   It's also going to complain if there's no middle name.

Here's what it's doing.

For last name:

      ,LEFT(Instr1_Name, CHARINDEX(',', Instr1_Name) - 1) AS LName

CHARINDEX returns an integer value telling you the first time if finds a comma in the string.  For us, we have "Smith," - the comma is at position 6, so we want to return the first 5 characters of the name (we don't want the comma).

For First Name, we know it starts the first character after the comma.

      ,SUBSTRING(Instr1_Name, CHARINDEX(',', Instr1_Name) + 1, CHARINDEX(' ', Instr1_Name) - CHARINDEX(',', Instr1_Name) - 1) AS FName

The parameters for Substring are (expression, starting pos, length)

The starting position is the first character after the comma.  The length of the string to return is from the starting position of the first name (column 7) to the position before the first space (in this case 11).  So we find the position of the space (11), the position of the comma (6) then subtract one more to get the length of the first name (11-6-1 = 4).

Finally for middle name:

       ,SUBSTRING(Instr1_Name, CHARINDEX(' ', Instr1_Name) + 1, len(Instr1_Name)) as MName

we can take position of the space, add 1 to get the first letter of the middle name and go until the end of the string.

For the original query you wrote in your question, with the nested CHARINDEX functions, CHARINDEX takes an optional third parameter "starting position".

So you could say "Find me the first space in this string, after character position 'x'".

What it looks like he's trying to do is to find the position of the first space after the first comma so he can feed a length into Substring - the extra space at the end is presumable a safety net so that if there isn't a space in the native string, he adds on at the end so it can find it.

So what we have above is hopefully a good starting point for you, and you can proceed from there to work out ways to handle middle middle names or slightly varied formatting (such as a space after the comma).

There's also a nicely detailed query that does lots of string manipulation on a single field with a name in it (although that name is formatted 'First Middle Last') here that caters for some of the exceptions I talked about above:

Author Comment

ID: 38770946
Thank you for so much detail.  I'll save this for future reference.

Author Comment

ID: 38779486
I'm posting my final code to show how I built on sjwales answer, but it now works if there is no middle name and if there is a space in the last name (eg: Van Halen).
FullName format is Last,First middle
    LEFT(FullName, CHARINDEX(',', FullName + ' ') - 1) AS LName,
                    CHARINDEX(',', FullName + ' ') + 1,
                    CHARINDEX(' ', FullName + ' ',CHARINDEX(',', FullName + ' ') + 1) - CHARINDEX(',', FullName + ' '))) as FName,
                    CHARINDEX(' ', FullName + ' ',CHARINDEX(',', FullName + ' ')),
                    CHARINDEX(' ', FullName + '  ', CHARINDEX(' ', FullName + ' ') + 1) - CHARINDEX(' ', FullName + ' '))) AS MI
    INTO #tmptbl FROM mytable

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Flattening heirachies 3 31
Help in Bulk Insert 9 36
SQL Syntax: How to force case sensitive query? 2 32
Syntax for query to update table 2 15
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question