Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
In sql server, I need to create a function to convert
'<sub>2c3</sub>' to  '<sub>2</sub><sub>c</sub><sub>3</sub>'
is there any easy to do that please.

the
'<sub>2c3</sub>'
is embedder in a string on a column in a table.

What I have tried:

I haven't got any idea. Tried to split the string first. but no sure how to handle if the string has more than one '' like 'this is 23 and this is another tp2'
Posted
Updated 3-Aug-22 4:11am
v2

1 solution

SQL
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

--declare @text varchar(2000) ='this is a <sub>2c3</sub>, and <sup>35</sup> this is another <sub>2p</sub> and end' 
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900