title: SQL for Splitting Names - Some with Middle Initial, Some Without
body: <p>I'm fairly new to SQL, I'm actually just building a small database in Access right now although if there is necessary functionality that Access can't do I'll remake the tables in SQL Server.</p>
  
  <p>Here's my situation, I have a list of names that come from a data dump from a third party. In our database I need to be able to compare first and last names in separate columns. </p>
  
  <p>I've been trying to use InStr, Left and Right - but am getting hung up with weird results</p>
  
  <pre><code>Left([NewClaims]![Claimant Full Name],InStr([NewClaims]![Claimant Full Name],",")-1) AS LastName, 
  Right([NewClaims]![Claimant Full Name],InStr([NewClaims]![Claimant Full Name], ", ")+2) AS FirstName,
  </code></pre>
  
  <p>On some names it works perfectly </p>
  
  <pre><code>West, Krystal --becomes--&gt; LastName = West, FirstName= Krystal
  </code></pre>
  
  <p>On other names, similar in formant it doesn't work</p>
  
  <pre><code>Dalton, Kathy ----&gt; LastName = Dalton, First Name = ON, KATHY
  </code></pre>
  
  <p>On Names with middle initials I get</p>
  
  <pre><code>Earles, Barbara A. ----&gt; LastName = Earles, FirstName= ARBARA A. (one missing letter) 
  </code></pre>
  
  <p>OR </p>
  
  <pre><code>Beard, Chekitha G. ----&gt; LastName = Beard, FirstName= KITHA G. (three missing letters)
  </code></pre>
  
  <p>I'm frustrated. Can anyone offer another idea on how to make this work? I seem to have the last name down, but I can't get the first name to be consistently correct. </p>