Nice. Can't help thinking, though, that recursive CTEs are a solution looking for a problem.

That method works well for short strings, but longer strings will soon hit the maximum recursion limit, and raising the recursion level means that the query can start to eat memory.

If you have a "free" tally table, then something iterative like this provides similar performance without the limitations of using recursion.

;with spc (id, num) as

(selecta.id, 0

from @vTable a

union all

select a.id, t.num

from @vTable a

inner join tally t

on t.num <= len(a.val) and SUBSTRING(a.val,t.num,1) = ' '

)

select a.id, substring(v.val, a.num + 1, coalesce(b.num-1, len(v.val)) - a.num) as Val

from @vTable v

inner join spc a on a.id = v.id

left join spc b on b.id = a.id and b.num > a.num

and not exists (

select 1

from spc c

where c.id = b.id and c.num > a.num and c.num < b.num

)

order by a.id, a.num