Solved

TSQL to Parse Name with comma

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

840 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