Not exactly what you might be looking for, but instead of trying to add commas after each character, you can use a subquery in the IN clause. Something like this:

WITH Tally(n) AS(

SELECT *

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))x(n))

SELECT NAME, SUBSTRING( NAME, n, 1)

from #temp te

JOIN Tally ta ON LEN( te.NAME) >= ta.n

Of course, the CTE can´t be in the subquery but it should be on the outer query.