Here's a method to parse the string by using a second delimited string of the keywords themselves used as the delimiting values. The strings are split using the DelimitedSplit8K function.

DECLARE

@s-2 VARCHAR(MAX)

,@Split CHAR(1)

,@KeyWords VARCHAR(50)

SET @Split = ' '

SELECT

@s-2 = 'Collapsed Statue: Matured Life # 007812 between BOKSAM KAND LAMOG and Bill APPER SRIM (Goper)'

,@KeyWords = 'between,and,(Goper)'

--other variations for testing

--@S = 'Collapsed Statue: XXXX YYYYY Matured Life # 007812 between BOKSAM KAND LAMOG and Bill APPER SRIM (Goper)'

--,@KeyWords = 'Statue:,between,and,(Goper)'

--@S = 'Collapsed Statue: Matured Life # 007812 between BOKSAM KAND LAMOG and Bill APPER SRIM (Goper) XXXX YYYYY'

--@S = 'Collapsed Statue: Matured Life # 007812 between BOKSAM ZZZZZ KAND LAMOG and Bill YYY APPER SRIM (Goper) XXXX YYYYY'

--,@KeyWords = 'Collapsed,YYYYY'

--,@KeyWords = '#,and,(Goper)'

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (

[n1] INT NOT NULL,

[i1] VARCHAR(50) NULL,

[n2] INT NULL,

[i2] VARCHAR(50) NULL,

PRIMARY KEY (n1))

INSERT INTO #TempTable

SELECT

dsk1.ItemNumber AS n1

,dsk1.Item AS i1

,dsk2.ItemNumber AS n2

,dsk2.Item AS i2

FROM

dbo.tvfDelimitedSplit8K(@S,' ') AS dsk1

LEFT OUTER JOIN

(

SELECT

Item

,ItemNumber

FROM

dbo.tvfDelimitedSplit8K(@KeyWords,',')

) AS dsk2

ON dsk1.Item = dsk2.Item

SELECT

t1.i1 AS item

FROM

#TempTable AS t1

INNER JOIN

#TempTable AS t2

ON t2.n1 = (SELECT n1 FROM #TempTable WHERE n1 > 0 GROUP BY n1 HAVING MIN(n2)=1)

INNER JOIN

#TempTable AS t3

ON t3.n1 = (SELECT n1 FROM #TempTable WHERE n1 > 0 GROUP BY n1 HAVING MAX(n2)=(SELECT COUNT(n2) FROM #TempTable))

WHERE

t1.n1 > t2.n1

AND t1.n1 < t3.n1

AND t1.n2 IS NULL