Another one:

declare @Word1 varchar(100), @Word2 varchar(100), @Word3 varchar(100)

declare @STR varchar(100) = 'Word1,Word2,Word3'

--declare @STR varchar(100) = ',Word2,'

--declare @STR varchar(100) = ',,Word3'

--declare @STR varchar(100) = 'Word1,,'

--declare @STR varchar(100) = ',,'

SELECT @Word1 = LEFT(@str, FC-1)

,@Word2 = SUBSTRING(@str, FC+1, SC-FC-1)

,@Word3 = SUBSTRING(@str, SC+1, LEN(@str))

FROM (SELECT @STR S) Q

CROSS APPLY (SELECT CHARINDEX(',',@str) FC) A1

CROSS APPLY (SELECT CHARINDEX(',',@str,A1.FC+1) SC) A2

select @Word1,@Word2,@Word3