Splitting Name field into First, Middle and Last Name

The following query is used to split up the FullName field of MS Access into First, Middle and Last Name based on delimiter space.


SELECT Left(FullName,instr(FullName , ' ')) AS [First Name], Right(FullName, ( Len

(FullName) - Instrev(FullName, ' ')) ) As [Last Name], Mid ( FullName, instr

(FullName , ' ') + 1, Instrev(FullName, ' ') - instr(FullName , ' ') - 1) As

[Middle Name]

FROM [Employee];



Assume the FullName Field of Employee table contains

FullName

Viji R Raj

Bob A Antony

Cath B Mony

OUTPUT

First Name Last Name Middle Name

Viji Raj R

Bob Antony A

Cath Mony B