Tuesday, June 30, 2009

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


Viji R Raj

Bob A Antony

Cath B Mony


First Name Last Name Middle Name

Viji Raj R

Bob Antony A

Cath Mony B

No comments:

Post a Comment