SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE FUNCTION [dbo].[tvf-Str-Extract] (@String varchar(max),@Delim1 varchar(100),@Delim2 varchar(100))
Returns Table 
As
Return (  

	Select RetSeq = row_number() over (order by RetSeq)
		  ,RetVal = left(RetVal,charindex(@Delim2,RetVal)-1)
	From  (
			Select RetSeq = row_number() over (order by 1/0)
				  ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
			From  ( values (convert(xml,'<x>' + replace((Select replace(@String,@Delim1,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>').query('.'))) as A(XMLData)
			Cross Apply XMLData.nodes('x') AS B(i)
		  ) C1
	Where charindex(@Delim2,RetVal)>1
)




Declare @YourTable Table ([SomeCol] varchar(max))  Insert Into @YourTable Values 
 ('<div/divdivdivdivdivp>12345</p/div/div/div/divdivdivspan>45678<span/span/span/div/divdivdiv>Test No.</div/divdivdivdivdivp>4466</p/div/div/div/divdiv>6789</divp/ppi>As per the attachment, no provided because we were supposed to be onboarded.</i/p>')
 
Select B.* 
 From @YourTable A
 Cross Apply [dbo].[tvf-Str-Extract](SomeCol,'>','</') B


RetSeq
RetVal


1
12345

2
Test No.

3
4466

4
6789

5
As per the attachment, no provided because we were supposed to be onboarded.


--If Passing a string
Declare @S varchar(max) = '<div/divdivdivdivdivp>12345</p/div/div/div/divdivdivspan>45678<span/span/span/div/divdivdiv>Test No.</div/divdivdivdivdivp>4466</p/div/div/div/divdiv>6789</divp/ppi>As per the attachment, no provided because we were supposed to be onboarded.</i/p>'

Select * from [dbo].[tvf-Str-Extract](@S,'>','</') B



RetSeq
RetVal


1
12345

2
Test No.

3
4466

4
6789

5
As per the attachment, no provided because we were supposed to be onboarded.

RetSeq	RetVal
1	12345
2	Test No.
3	4466
4	6789
5	As per the attachment, no provided because we were supposed to be onboarded.