Help with parsing a string

[eerich]

Hello - I've searched the forum and read through all of the SQL examples on InStr and InStrRev but I could not find an example that would help me do the following:

I have a username field with entries as such:
Dennis Johnson - AP Supervisor

I'm trying to parse the field into Firstname, LastName, JobTitle using SQL (not code module). My query thus far is:

SELECT
Left([UserNAME],InStr([UserNAME]," ")-1) as Firstname,

Left([UserNAME],InStrRev(rtrim([UserNAME]),'- ')-2) as LastName,

Mid([UserNAME],InStr([UserNAME],"-")+1) as Position

From Users;

The results for the 'Firstname' and 'Position' come back fine; however, the lastname does not work properly. It returns the First and Lastname together. I've experimented with all the examples in my search, however nothing has worked.

Any help would be greatly appreciated.

Thanks.

 

[Ed2020]

There's probably a far simpler way of doing this, but my suggestion is:

Mid([UserNAME], InStr(1, [UserNAME], " ") + 1, Len([UserNAME]) - Len(Right([UserNAME], InStrRev([UserNAME], "- ") + Len(Left([UserNAME], InStr([UserNAME], " "))))))

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

I was right, there is a much simpler way of doing it:

Mid([UserNAME], InStr(1, [UserNAME], " ") + 1, InStrRev([UserNAME], " -") - InStr(1, [UserNAME], " ") - 1)

Ed Metcalfe.

Please do not feed the trolls.....

 

[eerich]

Ed

That worked perfectly.

Thanks!