Are you talking about the following not returning any data?

select @datastring2 = @datastring2 + substring(@datastring,N,1)

from Tally

where n <=len(@datastring)

and substring(@datastring,N,1) <> ','

its because you are actually assigning the output of the query to @datastring2. when you assign a value to a variable using SELECT you get the last value returned from the query (When working with a scalar variable). your query assigns @datastring2 + the substring to the variable @datastring2 which builds the string you want to return.

to demonstrate the principal i will use integers and my tally table

DECLARE @demo INT = 0

SELECT @demo = @demo + N

FROM Tally

WHERE N <= 10

SELECT @demo

when you run your query you are "Adding" (Concatenating) the variable @datastring2 with the substring so instead of a value of 1+2+3+4+5+6+7+8+9+10 (55) you get your completed string assigned to your variable. once you have assigned a value to your variable the final SELECT @WhatEver returns the value.