Split / Parse last word in a field

[JohS]

I've a table with a field storing Fullname and tries to split that field
into FirstName and LastName.

To pick out Firstname goes well with this:

Left(Fullname,InStr(FullName," ")-1) AS FirstName¨

But how can I pick out the last word (I've decided to go for that as
LastName and will adjust the ones witch fails manually) as LastName?



I have names like these:



Nils Ole Astrup Hansen

Svein Henning Aukland

Anne Karin Bache

Geir Markhusen

Lars Christensen



Thanks,

JohS

 

[Guest]

Try something like

IIf(Instr([FullName]," ")=False,Null,Mid([FullName],InstrRev([FullName],"
")+1))

The first criteria check if there is more then one word in the name, if not
it return a Null
The InstrRev return the location of the last space, so the mid take the word
from that location + 1

 

[Jamie Collins]

I've a table with a field storing Fullname and tries to split that field
into FirstName and LastName.

To pick out Firstname goes well with this:

Left(Fullname,InStr(FullName," ")-1) AS FirstName¨

But how can I pick out the last word (I've decided to go for that as
LastName and will adjust the ones witch fails manually) as LastName?

Typically, the next question is, "How do I find the second to last
word?"

If you employ the standard trick of a Sequence table of unique
integers, you can use it to parse out delimited text in a column,
including a space-separated text. This example uses Northwind:

SELECT T1.CustomerID, S1.seq AS pos,
MID(T1.CompanyName, S1.seq, MIN(S2.seq) - S1.seq - 1) AS word
FROM Customers AS T1, Sequence AS S1, Sequence AS S2
WHERE MID(' ' & T1.CompanyName & ' ', S1.seq, 1) = ' '
AND MID(' ' & T1.CompanyName & ' ', S2.seq, 1) = ' '
AND S1.seq BETWEEN 1 AND 40
AND S2.seq BETWEEN 1 AND 40
AND S1.seq < S2.seq
GROUP BY T1.CustomerID, T1.CompanyName, S1.seq
HAVING LEN(MID(T1.CompanyName, S1.seq, MIN(S2.seq) - S1.seq - 1)) > 0
ORDER BY T1.CustomerID, S1.seq;

Obviously, finding the last word is a simply matter of finding the row
with MAX(pos).

Jamie.

--

 

[Guest]

After you have split the first and last names, you can simply query to
determine whether [FirstName] & " " & [LastName] <> [FullName] to identify
which records have have more than one "word" in the [FullName] field.

In my experience, addresses are the only thing harder to parse and interpret
than names.

Good luck
--
Email address is not valid.
Please reply to newsgroup only.

Try something like

IIf(Instr([FullName]," ")=False,Null,Mid([FullName],InstrRev([FullName],"
")+1))

The first criteria check if there is more then one word in the name, if not
it return a Null
The InstrRev return the location of the last space, so the mid take the word
from that location + 1

--
Good Luck
BS"D

I've a table with a field storing Fullname and tries to split that field
into FirstName and LastName.

To pick out Firstname goes well with this:

Left(Fullname,InStr(FullName," ")-1) AS FirstName¨

But how can I pick out the last word (I've decided to go for that as
LastName and will adjust the ones witch fails manually) as LastName?



I have names like these:



Nils Ole Astrup Hansen

Svein Henning Aukland

Anne Karin Bache

Geir Markhusen

Lars Christensen



Thanks,

JohS

 

[JohS]

Tanks to you all for feedback. And no, Sequence table of unique integers is
not known for me. But I have some clue of what you are doing here, but when
I pasted the code into a Query, I got error "cannot find the input table or
query 'Sequence'. Any idea what I do miss out? JohS

I've a table with a field storing Fullname and tries to split that field
into FirstName and LastName.

To pick out Firstname goes well with this:

Left(Fullname,InStr(FullName," ")-1) AS FirstName¨

But how can I pick out the last word (I've decided to go for that as
LastName and will adjust the ones witch fails manually) as LastName?

Typically, the next question is, "How do I find the second to last
word?"

If you employ the standard trick of a Sequence table of unique
integers, you can use it to parse out delimited text in a column,
including a space-separated text. This example uses Northwind:

SELECT T1.CustomerID, S1.seq AS pos,
MID(T1.CompanyName, S1.seq, MIN(S2.seq) - S1.seq - 1) AS word
FROM Customers AS T1, Sequence AS S1, Sequence AS S2
WHERE MID(' ' & T1.CompanyName & ' ', S1.seq, 1) = ' '
AND MID(' ' & T1.CompanyName & ' ', S2.seq, 1) = ' '
AND S1.seq BETWEEN 1 AND 40
AND S2.seq BETWEEN 1 AND 40
AND S1.seq < S2.seq
GROUP BY T1.CustomerID, T1.CompanyName, S1.seq
HAVING LEN(MID(T1.CompanyName, S1.seq, MIN(S2.seq) - S1.seq - 1)) > 0
ORDER BY T1.CustomerID, S1.seq;

Obviously, finding the last word is a simply matter of finding the row
with MAX(pos).

Jamie.

--

 

[Jamie Collins]

Tanks to you all for feedback. And no, Sequence table of unique integers is
not known for me. But I have some clue of what you are doing here, but when
I pasted the code into a Query, I got error "cannot find the input table or
query 'Sequence'. Any idea what I do miss out? JohS

For my example to work in Northwind, you first need to create a table
named Sequence with a column named seq of type INTEGER (Long Integer)
and populate it with forty rows with the values 1 to 40 respectively.

Jamie.

--