Script to Separate-First-Middle-Last-Name

/* Script to separate first ,Middle &  last Name from FullName in one column */
USENorthwind
go

CREATETABLETest
(
FullNameVARCHAR(40)
)

INSERTINTOTest
VALUES
(
'Mathew John Davis'
)


/* Variables Declaration */
DECLARE @var1 INT
DECLARE @var2 INT
DECLARE @var3 INT


/* select position for first blank space from column */
SELECT@var1=CHARINDEX(' ',FullName)
FROMTest

/* select first Name as follows from first position of column to first blank space */
SELECTSUBSTRING(FullName,1,@var1-1)
FROMTest

/* select position for second blank space from column */

select@var2=CHARINDEX(' ', SUBSTRING(FullName,@var1+1,LEN(FullName)))
FROMTest

/* select Middle Name as follows from  position of first blank space to second blank space */
selectSUBSTRING(FullName,@var1+1,@var2-1) 
FROMTest


/* select Last Name as rest of the string from second blank space to length of string */

SELECTSUBSTRING(FullName,@var1+@var2+1,LEN(FullName)) 
FROMTest