MS SQL - Split a String

This is the first time that i used the apply operator. As the name suggests the apply is an operator hence it works at each row level rather than at a data set level and this comes truly handy to solve the problem on hand.

As defined in TechNet : The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

I wanted to update a worker information and the data provided by the customer has the employee name in a single string using space as separator.

Below is the SQL statement for same.

select TCE.EmpCode   
    , substring( Emp.[Employee Name] , 1, P1.pos ) FirstName
    , case when p2.pos = 0 then '' else substring( Emp.[Employee Name] , P1.pos + 1, P2.pos - P1.pos) end MiddleName
    , case when p2.pos = 0 then substring( Emp.[Employee Name] , P1.pos + 1, len( Emp.[Employee Name] ) - P1.pos)
        else substring( Emp.[Employee Name] , P2.pos + 1, len([Employee Name]) - P2.pos) end LastName  
from TCE
left join EMP
on Emp.[Employee Number] = TCE.EmpCode
cross apply (select (charindex(' ', EMP.[Employee Name] ) ) ) as P1(pos)
cross apply (select (charindex(' ', EMP.[Employee Name] , P1.Pos + 1) ) ) as P2(pos)