/*
select cc.ContentID--, Text
, T1.* 
from (select  * from contents where text like '%#Link:Tran%' and contentID not in(Select fromid from transitions)) cc
cross apply vefn_FindTransPrefixAndSuffix(cc.text) t1



*/
alter  FUNCTION [dbo].[vefn_FindTransPrefixAndSuffix](@text varchar(MAX))
RETURNS @Transitions TABLE
(
  Prefix nvarchar(Max),
	Value nvarchar(Max),
	Suffix nvarchar(Max)
)
WITH EXECUTE AS OWNER
AS
BEGIN

 -- DECLARE @text nvarchar(max)
--SET @Text = 'PERFORM a Source Check PER Section \v <START]\v0 6.8, \ul FUNCTIONAL TEST OF 0\u8209?RIC\u8209?2201\ulnone \v #Link:Transition:11 794 78920[END>\v0 .'
--SET @Text = 'IMPLEMENT \v <START]\v0 EOP\u8209?8 rev, \ul FUNCTIONAL RECOVERY PROCEDURE\ulnone \ulnone\v #Link:Transition:0 127712 17827[END>\v0'
	DECLARE @index0 int
	DECLARE @index1 int
	DECLARE @index2 int
	DECLARE @index3 int
 	DECLARE @index4 int
	DECLARE @index5 int
	DECLARE @prefix nvarchar(max)
	DECLARE @value nvarchar(max)
	DECLARE @suffix nvarchar(max)
	DECLARE @rtext nvarchar(max)
	SET @rtext = Reverse(@Text)
	SET @index4 = CHARINDEX(' #Link:Transition' , @Text)
	WHILE (@index4 > 0)
  BEGIN
	SET @index1 = CHARINDEX(Reverse('<START]'),@rtext,len(@text)-@Index4)
	SET @index0 = len(@text) - CHARINDEX(' ',@rtext,@index1+1)-1
	SET @index1 = len(@text) - @index1+2
	SET @index2 = CHARINDEX(' ',@text,@index1)+1
	SET @prefix = substring (@Text,@index0 , @index2-@index0)
	--print 
	SET @index3 = len(@text) - CHARINDEX(' ',@rtext,len(@text)-@Index4)-1
	SET @index5 = CHARINDEX('[END>',@text, @index3)+5
	SET @index5 = charindex(' ',@text,@index5)+1
	if(@Index5 = 1) Set @Index5 = len(@text)+1
	--print @index5
	SET @value=substring(@text,@index2,@index3-@index2)
	--print 
	SET @suffix = substring(@text,@index3,@index5-@index3)
	--print '{' + @prefix + '}{' + @value + '}{' + @suffix + '}'
			INSERT INTO @Transitions VALUES (@Prefix,@Value,@Suffix)
		SET @index4 = CHARINDEX(' #Link:Transition' , @Text, @index5)
		END
	RETURN
END