create table # (id varchar(100))
select   id into #t from dbo.f_GetCommaListAsTable ('20,30,70 ,100 ')

select  (select  top 1 str(ID)  from #t)   + ' ,' +  str(id)  
from #t
where  id <> (select  top 1 str(ID)  from #t)

ALTER     Function  [dbo].[f_GetCommaListAsTableStrings] (@CommaLimitedList varchar(max))
RETURNS @Result TABLE (id varchar (50))
as
Begin

declare @CommList1 varchar (max)
declare  @Pos as int
declare  @tResult table(ID varchar (50))

set @CommList1 = LTrim(rTrim(@CommaLimitedList))
set @CommList1   =  @CommList1   + ' ,'
    WHILE   patindex ('%,%',@CommList1 )> 0
    BEGIN
        set @Pos  = patindex ('%,%',@CommList1 )
        insert @tResult select (left(@CommList1, @pos -1 ) )
        set @CommList1 = right (@CommList1 ,len(@CommList1)- @pos )
    end
/* при передаче пустого списка остается одна запись 0
ее удаляем
*/
delete from @tResult where id is null or  id  = ''
insert @Result
select rtrim ( Ltrim(id)) from @tResult
return
end

;with cte as
(select xz1, cast('<a>'+replace(xz2,',','</a><a>')+'</a>' as xml) as xz2 from @t)

select xz1, [val]
from cte t1
cross apply 
	(select t.c.value('text()[1]', 'varchar(10)') as [val]
	from t1.xz2.nodes('/a') as t(c))as t2

xz1                                                val
-------------------------------------------------- ----------
ул. Тестовая                                       1
ул. Тестовая                                       2
ул. Тестовая                                       3
ул. Тестовая                                       4
ул. Тестовая                                       5
ул. Тестовая                                       6
ул. Тестовая                                       7

declare @t table (s nvarchar(100));
insert into @t values (N'ул. Тестовая 1,2,3,4,5,6,7'), (N'ул. rrrrrrrrrrr and ggg  10,20,100');

with cte0 as(
select STUFF(s, CHARINDEX(',', s) - CHARINDEX(' ', REVERSE(SUBSTRING(s, 1, CHARINDEX(',', s) - 1))), 1, ',') as s
from @t)

,cte as(
select s, 
       number as pos, 
       dense_rank() over(partition by s order by number) as r,
       CHARINDEX(',', s) as pos1 
from cte0 t join master..spt_values v on v.type= 'P' and v.number < LEN(s)
where substring(s,number, 1) = ','
) 

select 
       substring(t1.s, 1, t1.pos1 - 1) + ' ' +
       substring(t1.s, t1.pos +1, isnull(t2.pos, LEN(t1.s) + 1) - t1.pos - 1)
from cte t1 left join cte t2 on t1.s= t2.s and t1.r + 1 =t2.r

ул. rrrrrrrrrrr and ggg  10
ул. rrrrrrrrrrr and ggg  20
ул. rrrrrrrrrrr and ggg  100
ул. Тестовая 1
ул. Тестовая 2
ул. Тестовая 3
ул. Тестовая 4
ул. Тестовая 5
ул. Тестовая 6
ул. Тестовая 7

declare @t table (s varchar(8000))
insert @t select 'ул. Тестовая 10,20,70' union all select 'ул. Самая Главная 175,5,95' union all select 'просто улица' union all select 'ул.'

;with t1 as (
  select isnull(left(s, len(s) + nullif(charindex(',',s),0)*0 - charindex(' ', reverse(s))), s) [s],
  stuff(s, 1+len(s) + nullif(charindex(',',s),0)*0 - charindex(' ', reverse(s)), 1, ',') + ',' [a] from @t
), t2 as (
  select s, substring(a,t+1,8000) [a], left(a,t-1) [st]
  from t1 outer apply(select charindex(',',a)t)t
  union all
  select s, substring(a,t+1,8000), left(a,t-1)
  from t2 cross apply(select charindex(',',a)t)t
  where t > 0
) 
select isnull(s +' '+ st, s) [Result] from t2
where (s <> st) or (st is null)
option (maxrecursion 0)