--data
declare @names table (id int, v varchar(8000))
insert @names
          select 1, 'kelly.yap, lizzy.fox, yahoo, finance.dep, hope.miller, porly.john'
union all select 2, 'this, is, another, line, of, text, some, values, have, two words, others have three'
union all select 3, 'this, is, a, third, line'

--calculation
select
  id,
  ltrim(substring(v, NUMBER, charindex(',', substring(v + ',', NUMBER, 8000))-1)) as name
from dbo.F_TABLE_NUMBER_RANGE(1, 8000), @names
where substring(',' + v, NUMBER, 1) = ','
order by id

/*results
id          name                       
----------- ---------------------------
1           kelly.yap
1           lizzy.fox
1           yahoo
1           finance.dep
1           hope.miller
1           porly.john
2           this
2           is
2           another
2           line
2           of
2           text
2           some
2           values
2           have
2           two words
2           others have three
3           this
3           is
3           a
3           third
3           line
*/