How to seperate string into 3 columns

Question

I have a column of user names.

They are as follows:

'first       last      middleinitial'

Notice the large spaces between the name parts, these are always a different number of spaces.

Question:

How would I separate first, last, and middleinitial into separate columns (even if the spaces are different for every name)?

Answer 1

WITH t AS
(
    SELECT 'first       last      middleinitial' AS name
)

SELECT 
LEFT(name,CHARINDEX(' ', name)-1) 
,RIGHT(name, CHARINDEX(' ', REVERSE(name))-1)
,LTRIM(RTRIM(SUBSTRING(name,CHARINDEX(' ', name),LEN(name)- CHARINDEX(' ', REVERSE(name))-CHARINDEX(' ', name))))
FROM t