use tempdb;
set ansi_nulls, quoted_identifier, xact_abort, nocount on;
go

CREATE or alter FUNCTION dbo.Replacetxt (@TXT AS NVARCHAR(50))
RETURNS NVARCHAR(250)
AS
BEGIN
	DECLARE @Text NVARCHAR(250)= ''

	SELECT
		@Text= REPLACE(REPLACE(@Text + QUOTENAME(C), '[', ''), ']', '')
	FROM
		(
		SELECT T, IIF(R= 1, A+B,','+A+B) C
		FROM
			(
				SELECT
					ROW_NUMBER() OVER (ORDER BY @TXT) R
					,SUBSTRING(@TXT, 0, PATINDEX('%[^0-9]%', @TXT)) T
					,SUBSTRING(@TXT, 0, PATINDEX('%[^0-9]%', @TXT)) A, SUBSTRING(REPLACE(@TXT, SUBSTRING(@TXT, 0, PATINDEX('%[^0-9]%', @TXT)), ''),number,1) B
				FROM MASTER.dbo.spt_values
				WHERE TYPE='P' AND NUMBER BETWEEN 1 AND LEN(REPLACE(@TXT, SUBSTRING(@TXT, 0, PATINDEX('%[^0-9]%', @TXT)), ''))
			) D
		) TB

	RETURN @Text
END
go

declare @r table (descr varchar(50), elapsed_time_ms int);

drop table if exists #t;
create table #t (id integer identity primary key, txt varchar(100));

insert into #t (txt)
select
 d.s
from
 (select top (50000) a.number from master.dbo.spt_values a cross join master.dbo.spt_values b) c cross apply
 (values ('1ABCD'), ('1AC'), ('1ACE'), ('2A'), ('2AC'), ('3ABCDEFGHIJKLMNOPQRSTUVWXYZ')) d(s);

declare @dt datetime2 ;

begin tran;

set @dt = sysdatetime();
declare @n int = ( select max(len(txt)) from #t );

while @n > 2 begin

  update #t set txt = stuff(txt, @n, 0, ',' + left(txt, 1) )
    where len(txt) >= @n;

  set @n = @n - 1;

end;

insert into @r
select 'cycle by aleks222', datediff(ms, @dt, sysdatetime());

rollback;

begin tran;

set @dt = sysdatetime();

update #t
 set
  txt = stuff(RegexpReplaceWrapper(substring(txt, 2, cast(0x7fffffff as int)), '(.{1})', ',' + left(txt, 1) + '$1', 'default'), 1, 1, '')
from
 #t;

insert into @r
select 'RegexpReplace wrapper', datediff(ms, @dt, sysdatetime());

rollback;

begin tran;

set @dt = sysdatetime();

with x as
(
 select
  t.txt, b.txt_new
 from
  #t t cross apply
  (
   select
    string_agg(a.s, ',') within group (order by a.rn)
   from
    (
     select top (len(t.txt) - 1)
      row_number() over (order by 1/0) as rn,
      left(t.txt, 1) + substring(t.txt, row_number() over (order by 1/0) + 1, 1)
     from
      master.dbo.spt_values
    ) a(rn, s)
  ) b(txt_new)
)
update x
 set
  txt = txt_new;

insert into @r
select 'split and string_agg b invm', datediff(ms, @dt, sysdatetime());

rollback;

begin tran;

set @dt = sysdatetime();

;with cte as (
	select
		t.id,
		t.txt,
		a.number,
		a.ch
	from #t t
	cross apply (select v.number, substring(t.txt, 1+v.number, 1) as ch from master..spt_values v 
                           where v.number < len(t.txt) and v.type = 'P') a
),
s as
(
 select
 	t1.id,
 	string_agg(t1.ch + t2.ch, ',') within group(order by t2.number) as xz
 from cte t1 inner join cte t2 on t1.id=t2.id 
 where t1.number = 0 and t2.number <> 0
 group by t1.id
)
update t
 set
  txt = s.xz
from
 s join
 #t t on t.id = s.id;

insert into @r
select 'by court', datediff(ms, @dt, sysdatetime());

rollback;

begin tran;

set @dt = sysdatetime();

update #t set txt = dbo.Replacetxt(txt);

insert into @r
select 'scalar function by MaksK', datediff(ms, @dt, sysdatetime());

rollback;

select * from @r order by elapsed_time_ms;
go

drop function dbo.Replacetxt;
go

use tempdb;
set ansi_nulls, quoted_identifier, xact_abort, nocount on;
go

CREATE or alter FUNCTION dbo.Replacetxt (@TXT AS NVARCHAR(50))
RETURNS NVARCHAR(250)
AS
BEGIN
	DECLARE @Text NVARCHAR(250)= ''

	SELECT
		@Text= REPLACE(REPLACE(@Text + QUOTENAME(C), '[', ''), ']', '')
	FROM
		(
		SELECT T, IIF(R= 1, A+B,','+A+B) C
		FROM
			(
				SELECT
					ROW_NUMBER() OVER (ORDER BY @TXT) R
					,SUBSTRING(@TXT, 0, PATINDEX('%[^0-9]%', @TXT)) T
					,SUBSTRING(@TXT, 0, PATINDEX('%[^0-9]%', @TXT)) A, SUBSTRING(REPLACE(@TXT, SUBSTRING(@TXT, 0, PATINDEX('%[^0-9]%', @TXT)), ''),number,1) B
				FROM MASTER.dbo.spt_values
				WHERE TYPE='P' AND NUMBER BETWEEN 1 AND LEN(REPLACE(@TXT, SUBSTRING(@TXT, 0, PATINDEX('%[^0-9]%', @TXT)), ''))
			) D
		) TB

	RETURN @Text
END
go

drop table if exists #t;

create table #t (id integer identity, txt varchar(100), l as len(txt) persisted);

insert into #t (txt)
select
 d.s
from
 (select top (50000) a.number from master.dbo.spt_values a cross join master.dbo.spt_values b) c cross apply
 (values ('1ABCD'), ('1AC'), ('1ACE'), ('2A'), ('2AC'), ('3ABCDEFGHIJKLMNOPQRSTUVWXYZ')) d(s);

--create clustered index IX_t__l on #t(l);

begin tran;

declare @n int = ( select max(l) from #t );

while @n > 2 begin

  update/*cycle by aleks222*/ #t set txt = stuff(txt, @n, 0, ',' + left(txt, 1) )
    where l >= @n;

  set @n = @n - 1;

end;

rollback;

begin tran;

update /*RegexpReplace wrapper*/ #t
 set
  txt = stuff(RegexpReplace(substring(txt, 2, cast(0x7fffffff as int)), '.', ',' + left(txt, 1) + '$0'), 1, 1, '')
from
 #t;

rollback;

begin tran;

with x as
(
 select
  t.txt, b.txt_new
 from
  #t t cross apply
  (
   select
    string_agg(a.s, ',') within group (order by a.rn)
   from
    (
     select top (len(t.txt) - 1)
      row_number() over (order by 1/0) as rn,
      left(t.txt, 1) + substring(t.txt, row_number() over (order by 1/0) + 1, 1)
     from
      master.dbo.spt_values
    ) a(rn, s)
  ) b(txt_new)
)
update /*split and string_agg by invm*/ x
 set
  txt = txt_new
option
 (maxdop 1);

rollback;

begin tran;

with x as
(
 select
  t.txt, b.txt_new
 from
  #t t cross apply
  (
   select
    string_agg(a.s, ',') within group (order by a.rn)
   from
    (
     select top (len(t.txt) - 1)
      row_number() over (order by 1/0) as rn,
      left(t.txt, 1) + substring(t.txt, row_number() over (order by 1/0) + 1, 1)
     from
      master.dbo.spt_values
    ) a(rn, s)
  ) b(txt_new)
)
update /*split and string_agg by invm (optimization spool+sort off)*/ x
 set
  txt = txt_new
option
 (maxdop 1, queryruleoff BuildSpool);

rollback;

begin tran;

;with cte as (
	select
		t.id,
		t.txt,
		a.number,
		a.ch
	from #t t
	cross apply (select v.number, substring(t.txt, 1+v.number, 1) as ch from master..spt_values v 
                           where v.number < len(t.txt) and v.type = 'P') a
),
s as
(
 select
 	t1.id,
 	string_agg(t1.ch + t2.ch, ',') within group(order by t2.number) as xz
 from cte t1 inner join cte t2 on t1.id=t2.id 
 where t1.number = 0 and t2.number <> 0
 group by t1.id
)
update /*by court*/ t
 set
  txt = s.xz
from
 s join
 #t t on t.id = s.id;

rollback;

begin tran;

update /*scalar function by MaksK*/ #t set txt = dbo.Replacetxt(txt);

rollback;

with r as
(
 select
  a.descr
 from
  (
   values
    ('split and string_agg by invm'), ('by court'), ('RegexpReplace wrapper'), ('cycle by aleks222'), ('scalar function by MaksK'),
    ('split and string_agg by invm (optimization spool+sort off)')
  ) a(descr)
),
qs as
(
 select top (1) with ties
  r.descr, qs.execution_count, qs.total_worker_time, qs.total_elapsed_time, qs.creation_time,
  a.statement_text, cast(qp.query_plan as xml) as query_plan
 from
  sys.dm_exec_query_stats qs join
  sys.dm_exec_cached_plans cs on cs.plan_handle = qs.plan_handle cross apply
  sys.dm_exec_sql_text(qs.sql_handle) st cross apply
  sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) qp cross apply
  (
   select substring(st.text, (qs.statement_start_offset/2)+1,   
          ((case qs.statement_end_offset  
             when -1 then datalength(st.text)  
             else qs.statement_end_offset  
            end - qs.statement_start_offset)/2) + 1)
 ) a(statement_text) join
 r on a.statement_text like '%/*' + r.descr + '*/%'
 order by
  row_number() over (partition by r.descr order by qs.creation_time desc)
)
select
 *
from
 qs
order by
 total_elapsed_time
option
 (recompile);
go

drop function dbo.Replacetxt;
go