This solution makes use of techniques described by Jeff Moden in this article on the Running Sum problem in SQL. This solution is lengthy, but by making use of the Quirky Update in SQL Server over a clustered index, holds the promise of being much more efficient over large data sets than cursor-based solutions.
Update - amended below to operate off a table of strings
Assuming the existence of a tally table created like this (with at least 8000 rows):
create table dbo.tally (
N int not null
,unique clustered (N desc)
);
go
with
E1(N) as (
select 1 from (values
(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1)
) E1(N)
), --10E+1 or 10 rows
E2(N) as (select 1 from E1 a cross join E1 b), --10E+2 or 100 rows
E4(N) As (select 1 from E2 a cross join E2 b) --10E+4 or 10,000 rows max
insert dbo.tally(N)
select row_number() over (order by (select null)) from E4;
go
and a HtmlTagSpotter function defined like this:
create function dbo.HtmlTagSPotter(
@pString varchar(8000)
,@pDelimiter char(2))
returns table with schemabinding as
return
WITH
Delimiter as (
select len(@pDelimiter) as Length,
len(@pDelimiter)-1 as Offset
),
cteTally(N) AS (
select top (isnull(datalength(@pstring),0))
row_number() over (order by (select null))
from dbo.tally
),
cteStart(N1) AS (--==== Returns starting position of each "delimiter" )
select
t.N
from cteTally t cross join Delimiter
where substring(@pString, t.N, Delimiter.Length) = @pDelimiter
),
cteValues as (
select
ItemNumber = row_number() over(order by N1)
,Location = N1
from cteStart
)
select
ItemNumber
,Location
from cteValues
go
then running the following SQL will perform the required substitution. Note that the inner join at the end prevents any trailing "odd" tags from being converted:
create table #t(
ItemNo int not null
,Item varchar(8000) null
,StartLocation int not null
,EndLocation int not null
,constraint PK unique clustered (ItemNo,StartLocation desc)
);
with data(i,s) as ( select i,s from (values
(1,'Questions about **general computing hardware and software** are off-topic **for Stack Overflow.')
,(2,'Questions **about **general computing hardware and software** are off-topic **for Stack Overflow.')
--....,....1....,....2....,....3....,....4....,....5....,....6....,....7....,....8....,....9....,....0
)data(i,s)
),
tags as (
select
ItemNo = data.i
,Item = data.s
,Location = f.Location
,IsOpen = cast((TagNumber % 2) as bit)
,Occurrence = TagNumber
from data
cross apply dbo.HtmlTagSPotter(data.s,'**') f
)
insert #t(ItemNo,Item,StartLocation,EndLocation)
select
data.ItemNo
,data.Item
,prev.Location
,data.Location
from tags data
join tags prev
on prev.ItemNo = data.ItemNo
and prev.Occurrence = data.Occurrence - 1
and prev.IsOpen = 1
union all
select
i,s,8001,8002
from data
;
declare @ItemNo int
,@ThisStting varchar(8000);
declare @s varchar(8000);
update this
set @s = this.Item = case when this.StartLocation > 8000
then this.Item
else stuff(stuff(@s,this.EndLocation, 2,'</b>')
,this.StartLocation,2,'<b>')
end
from #t this with (tablockx)
option (maxdop 1);
select
Item
from (
select
Item
,ROW_NUMBER() over (partition by ItemNo order by StartLocation) as rn
from #t
) t
where rn = 1
go
yielding:
Item
------------------------------------------------------------------------------------------------------------
Questions about <b>general computing hardware and software</b> are off-topic **for Stack Overflow.
Questions <b>about </b>general computing hardware and software<b> are off-topic </b>for Stack Overflow.
