Split / Parse last word in a field

  • Thread starter Thread starter JohS
  • Start date Start date
J

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
 
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
 
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?
Click to expand...

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.

--
 
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.


Ofer Cohen said:
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


JohS said:
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
Click to expand...
Click to expand...
 
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?
Click to expand...

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.

--
 
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
Click to expand...

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.

--
 
Back
Top