DECLARE
    @S VARCHAR(MAX)
   ,@Split CHAR(1)
   ,@KeyWords VARCHAR(50)

SET @Split = ' '

SELECT
    @S = '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