I have several full name and I'd like to split into last, first and mid name please help

Ex:
Smith, John T
Nguyen, Hung F
Johnson, Paul

I'd like to split as following
LastName FirstName Mid
Smith John T
Nguyen Hung F
Johnson Paul

PLease help Thanks

if you're using vbscript try this, let say fullname="Smith, John T"
1. replace (,) with ("")
fullname=replace(fullname,",","")

2. split the string where found space (" ")
nameArray=split(fullname," ")

now you're have have array of lastname, firstname and mid where
LastName = nameArray(1)
FirstName = nameArray(2)
Mid = nameArray(3)

I am not sure an Array element starting 0 or 1, if starting nameArray(1) doesn't work try nameArray(0) then.

goodluck

Hi, I have the same plan.

In fact I did:

' varSCSRID is Lastname + Firstname
splitArray = Split(varSalesRep, " ")
varSCSRID = Replace(UCase(splitArray(1)), "'", "''") & Replace(UCase(splitArray(0)), "'", "''")

The problem becomes, what if you have "bad" data entry and you get first name with 2 or more spaces until the last name?

This happened to me...

I am trying to code a solution...

Possible to Trim the array values?

-- prepare test data
declare @test table (Name varchar(200))

insert @test
select 'Smith, John T' union all
select 'Nguyen, Hung F' union all
select 'Johnson, Paul'

select * from @test

-- do the work
select LEFT(Name, CHARINDEX(', ', Name) - 1) LastName,
        SUBSTRING(Name, CHARINDEX(', ', Name) + 2, CASE WHEN CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) = 0 THEN LEN(Name) + 1 ELSE CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) END - CHARINDEX(', ', Name) - 2) FirstName,
        RIGHT(Name, LEN(Name) - CASE WHEN CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) = 0 THEN LEN(Name) ELSE CHARINDEX(' ', Name, CHARINDEX(', ', Name) + 2) END) Mid
from @test

Another way using PARSENAME

-- prepare test data
declare @test table (Name varchar(200))

insert @test
select 'Smith, John T' union all
select 'Nguyen, Hung F' union all
select 'Johnson, Paul'

select * from @test

-- do the work
select CASE LEN(NAME) -LEN(Replace(NAME,' ','')) WHEN 2 then REPLACE(PARSENAME(REPLACE(Name,' ','.'),3),',','')
        else REPLACE(PARSENAME(REPLACE(Name,' ','.'),2),',','') end LastNAme,
CASE LEN(NAME) -LEN(Replace(NAME,' ','')) WHEN 2 then PARSENAME(REPLACE(Name,' ','.'),2)
        else PARSENAME(REPLACE(Name,' ','.'),1) end FirstNAme,
CASE LEN(NAME) -LEN(Replace(NAME,' ','')) WHEN 2 then PARSENAME(REPLACE(Name,' ','.'),1)
        else '' end MidNAme
from @test



------------------------------------------ http://sqlservercode.blogspot.com/