Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 914
  • Last Modified:

TSQL to Parse Name with comma

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
  • 2
1 Solution
Steve WalesSenior Database AdministratorCommented:
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:
scover22Author Commented:
Thank you for so much detail.  I'll save this for future reference.
scover22Author Commented:
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now