07-31-2023, 09:58 AM
A recursive cte based solution
declare @T table (iden int identity, col1 varchar(100));
insert into @T(col1) values
('ROOT/South America/Lima/Test/Test2')
, ('ROOT/South America/Peru/Test/Test2')
, ('ROOT//South America/Venuzuala ')
, ('RtT/South America / ')
, ('ROOT/South Americas// ');
declare @split char(1) = '/';
select @split as split;
with cte as
( select t.iden, case when SUBSTRING(REVERSE(rtrim(t.col1)), 1, 1) = @split then LTRIM(RTRIM(t.col1)) else LTRIM(RTRIM(t.col1)) + @split end as col1, 0 as pos , 1 as cnt
from @T t
union all
select t.iden, t.col1 , charindex(@split, t.col1, t.pos + 1), cnt + 1
from cte t
where charindex(@split, t.col1, t.pos + 1) > 0
)
select t1.*, t2.pos, t2.cnt
, ltrim(rtrim(SUBSTRING(t1.col1, t1.pos+1, t2.pos-t1.pos-1))) as bingo
from cte t1
join cte t2
on t2.iden = t1.iden
and t2.cnt = t1.cnt+1
and t2.pos > t1.pos
order by t1.iden, t1.cnt;
declare @T table (iden int identity, col1 varchar(100));
insert into @T(col1) values
('ROOT/South America/Lima/Test/Test2')
, ('ROOT/South America/Peru/Test/Test2')
, ('ROOT//South America/Venuzuala ')
, ('RtT/South America / ')
, ('ROOT/South Americas// ');
declare @split char(1) = '/';
select @split as split;
with cte as
( select t.iden, case when SUBSTRING(REVERSE(rtrim(t.col1)), 1, 1) = @split then LTRIM(RTRIM(t.col1)) else LTRIM(RTRIM(t.col1)) + @split end as col1, 0 as pos , 1 as cnt
from @T t
union all
select t.iden, t.col1 , charindex(@split, t.col1, t.pos + 1), cnt + 1
from cte t
where charindex(@split, t.col1, t.pos + 1) > 0
)
select t1.*, t2.pos, t2.cnt
, ltrim(rtrim(SUBSTRING(t1.col1, t1.pos+1, t2.pos-t1.pos-1))) as bingo
from cte t1
join cte t2
on t2.iden = t1.iden
and t2.cnt = t1.cnt+1
and t2.pos > t1.pos
order by t1.iden, t1.cnt;