Solved

TSQL to Parse Name with comma

Posted on 2013-01-12
3
803 Views
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.  
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27346147.html

The code I'm using is:
SELECT DISTINCT Instr1_Name,
     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
Name-Parsing-Results.png
0
Comment
Question by:scover22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
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:
http://stackoverflow.com/questions/159567/how-can-i-parse-the-first-middle-and-last-name-from-a-full-name-field-in-sql
0
 

Author Comment

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

Author Comment

by:scover22
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
SELECT DISTINCT FullName,
    LEFT(FullName, CHARINDEX(',', FullName + ' ') - 1) AS LName,
   RTRIM(SUBSTRING(FullName,
                    CHARINDEX(',', FullName + ' ') + 1,
                    CHARINDEX(' ', FullName + ' ',CHARINDEX(',', FullName + ' ') + 1) - CHARINDEX(',', FullName + ' '))) as FName,
    LTRIM(SUBSTRING(FullName,
                    CHARINDEX(' ', FullName + ' ',CHARINDEX(',', FullName + ' ')),
                    CHARINDEX(' ', FullName + '  ', CHARINDEX(' ', FullName + ' ') + 1) - CHARINDEX(' ', FullName + ' '))) AS MI
    INTO #tmptbl FROM mytable
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 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