use tempdb;
go

create function dbo.fnSplitString
(
 @s nvarchar(max),
 @delimeter nvarchar(100) = N','
)
returns table
as
return (
 with s as
 (
  select
   c.d, d.s
  from
   (select N'[' + @delimeter + N']') a(d) cross apply
   (select replace(@s, @delimeter, a.d)) b(s) cross apply
   (select cast(cast('' as xml).query('sql:column("a.d")') as varchar(max))) c(d) cross apply
   (select cast(cast('' as xml).query('sql:column("b.s")') as varchar(max))) d(s)
 )
 select
  row_number() over (order by (select 1)) as ValueOrder,
  b.n.value('.', 'nvarchar(max)') as Value
 from
  (select cast(N'<item>' + replace(s.s, s.d, N'</item><item>') + N'</item>' as xml) from s) a(x) cross apply
  a.x.nodes('/item') b(n)
);
go

create function dbo.fn1
(
 @s VARCHAR(200)
)
returns table
as
return (
WITH
[nn] AS (
  SELECT
    [rn]
  FROM
    ( SELECT [rn] = ROW_NUMBER() OVER ( ORDER BY 1/0 ) FROM master..spt_values ) t
  WHERE
    [rn] <= LEN( @s )
)
SELECT
  [value] = LEFT( @s, [rn] )
FROM
  [nn]
WHERE
  SUBSTRING( @s, [rn], 1 ) = ']'
);
go

create function dbo.fn2
(
 @s VARCHAR(200)
)
returns table
as
return (
with
cte as
(
	select
		left(@s, dat.i) as value,
		dat.i as i
	from
		(select CHARINDEX(']', @s) as i) as dat
	union all
	select
		left(@s, dat.i) as value,
		dat.i
	from
		(select CHARINDEX(']', @s, cte.i + 1) as i from cte) as dat
	where
		dat.i > 0
)
select value from cte
)
go

create function dbo.fn3
(
 @s VARCHAR(200)
)
returns table
as
return (
 with t(i, p, is_bracketed_number) as
 (
  select
   ValueOrder,
   value,
   case when value like '![%!]' escape '!' then 1 else 0 end
  from
   dbo.fnSplitString(@s, '\')
  where
   value > ''
 )
 select
  b.x.value('.', 'varchar(100)') as value
 from
  (select i from t where is_bracketed_number = 1) a cross apply
  (select '\' + p from t where i <= a.i order by i for xml path(''), type) b(x)
);
go

declare @s varchar(200)	= '\ext_bulk_insert\[2]\product\[5]\product_layout\loop\[1]\delete ext';

create table dbo.t (id int identity primary key, s varchar(200));

insert into dbo.t
select top (1000000)
 @s
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;
go

declare @c int;

set statistics time on;

select
 @c = count(*)
from
 dbo.t cross apply
 dbo.fn1(s) f
option
 (maxdop 1);

select
 @c = count(*)
from
 dbo.t cross apply
 dbo.fn2(s) f
option
 (maxdop 1);

select
 @c = count(*)
from
 dbo.t cross apply
 dbo.fn3(s) f
option
 (maxdop 1);

set statistics time off;
go

drop table dbo.t;
drop function dbo.fn1, dbo.fn2, dbo.fn3, dbo.fnSplitString;
go

(1000000 rows affected)

 Время работы SQL Server:
   Время ЦП = 22917 мс, затраченное время = 23102 мс.

 Время работы SQL Server:
   Время ЦП = 47689 мс, затраченное время = 48765 мс.

 Время работы SQL Server:
   Время ЦП = 1248 мс, затраченное время = 1249 мс.