CODEWITHSUNDEEP
sqlsql-server
user3253692
user3253692

Reputation: 11

SQL for Splitting Names - Some with Middle Initial, Some Without

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.

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.

I've been trying to use InStr, Left and Right - but am getting hung up with weird results

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,

On some names it works perfectly 

West, Krystal --becomes--> LastName = West, FirstName= Krystal

On other names, similar in formant it doesn't work

Dalton, Kathy ----> LastName = Dalton, First Name = ON, KATHY

On Names with middle initials I get

Earles, Barbara A. ----> LastName = Earles, FirstName= ARBARA A. (one missing letter)

OR 

Beard, Chekitha G. ----> LastName = Beard, FirstName= KITHA G. (three missing letters)

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.

Upvotes: 1

Views: 1082

Answers (1)

AdamL
AdamL

Reputation: 13151

Try this. But I'm assuming that there's always a comma that separates last name from first name.

select
    txt,
    LastName = left(txt,charindex(',',txt)-1),
    FirstName = ltrim(right(txt,len(txt)-charindex(',',txt)))
from (
    select 'West, Krystal' as txt union all
    select 'Dalton, Kathy' union all
    select 'Earles, Barbara A.' union all
    select 'Beard, Chekitha G.'
) x

Your mistake was that when using right to extract first name, you didn't take the length of the string under consideration.

Upvotes: 2

Related Questions