Solved

TSQL to Parse Name with comma

Posted on 2013-01-12
3
714 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
  • 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
execute a MS SQL script as a schedule SQL job 72 99
Date conversion in sql server 2012 6 26
Mssql SQL query 14 28
Sql query 34 20
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now