CREATE function [dbo].[fn_breakSub_string] (
@Sentence VARCHAR(8000)
)
returns varchar(8000)
AS
BEGIN
DECLARE @t VARCHAR(20) =''
DECLARE @r VARCHAR(8000) =''
DECLARE @Words varchar(8000) =''
DECLARE @I INT= 1
SET @Words = @Sentence
WHILE(@I < LEN(@Words)+1)
BEGIN
set @t = '<sub>'+SUBSTRING(@words,@I,1)+'</sub>'
set @r =@r+@t
SET @I = @I + 1
END
return @r
END
CREATE function [dbo].[fn_parseSubSup] ( @text VARCHAR(8000))
returns varchar(8000)
AS
BEGIN
select @text= ' '+replace(replace(@text,'<sub>','~'),'</sub>','| ')
;with a as (Select items blk from dbo.Split(@text, '~'))
, b as (select value items from a OUTER APPLY STRING_SPLIT(a.blk,'|') sp)
, c as (select CASE when (items not like ' %' and items not like ',%') Then [dbo].[fn_breakSub_string](items) ELSE items END as w_sub from b )
select @text= STRING_AGG( ISNULL(w_sub, ' '), '') From c
return @text
END
SELECT [dbo].[fn_parseSubSup]('this is a test <sub>2c3</sub>, this is another test <sub>test</sub> end.') as title