-- Вспомогательная таблица чисел
set nocount on;
declare @numbers table(n int primary key);
declare @i int;
set @i = 1;
while @i <= 1000
begin
insert @numbers values(@i);
set @i = @i + 1;
end;
-- Подготовка данных
declare @tab table (id int, string varchar(1000));
insert @tab values(1, 'дней /5/ и /20/');
insert @tab values(2, 'дней /11/ и / 15 / и /30 / ');
insert @tab values(3, 'дней /2/ и/6/ и /7 ');
insert @tab values(4, '/////');
insert @tab values(5, '<Разные XML-символы> '' " /test/');
-- Преобразование данных
with cte as
( select t.id, t.string, n.n,
substring(t.string, n.n, charindex('/', '/' + t.string + '/', n.n + 1) - n.n - 1) as part,
row_number() over(partition by t.id order by n.n) % 2 as flag
from @tab t
inner join @numbers n on n.n <= len(t.string) + 1 and substring('/' + t.string + '/', n.n, 1) = '/'
)
select c.id, c.string, z.new_string
from cte c
cross apply
( select stuff
( ( select case when c1.flag = 1 then ')' else '(' end + c1.part
from cte c1
where c1.id = c.id
order by c.n
for xml path(''), type
).value('.', 'varchar(max)'),
1, 1, ''
) new_string
) z
where c.n = 1;