SELECT

AccountNumber,

Name,

Name = LEFT(Name,space1.pos-1),

Name = SUBSTRING(Name,space1.pos+1,space2.pos-space1.pos-1),

Name = SUBSTRING(Name,space2.pos+1,40)

FROM dbo.AccountBase

CROSS APPLY (SELECT CHARINDEX(' ', Name,1)) space1 (pos)

CROSS APPLY (SELECT CHARINDEX(' ', Name,space1.pos+1)) space2 (pos)

But the above creates an error

Msg 537, Level 16, State 3, Line 5

Invalid length parameter passed to the LEFT or SUBSTRING function.

??