Identify record sequences matching a predefined pattern in SQL Server

We have a set of integer values that define a pattern: {5, 2, 6, 8}.

Our aim is to identify record sequences that match this pattern and return them with a SELECT query.

With the following table as input:

Id	Val
1	5
2	2
3	6
4	8
5	5
6	2
7	5
8	2
9	6
10	8

we want to produce the following output:

Id	Val
1	5
2	2
3	6
4	8
7	5
8	2
9	6
10	8

Field Id is an auto-increment primary key that uniquely identifies table records and also determines row order.

The first thing we need to do is add sequence numbers to the set that defines the pattern. We can use the following query wrapped in a Common Table Expression (CTE) to do so:

;WITH Seq AS (
    SELECT v, ROW_NUMBER() OVER(ORDER BY k) AS rn
    FROM (VALUES(1, 5), (2, 2), (3, 6), (4, 8)) x(k,v)
)

Output:

v	rn
5	1
2	2
6	3
8	4

Using the above CTE we can identify islands, i.e. slices of sequential rows containing the whole of the sequence:

;WITH Seq AS (
    SELECT v, ROW_NUMBER() OVER(ORDER BY k) AS rn
    FROM (VALUES(1, 5), (2, 2), (3, 6), (4, 8)) x(k,v)
), Grp AS (
SELECT Id, Val, 
       ROW_NUMBER() OVER (ORDER BY Id) - rn AS grp            
FROM mytable AS m
LEFT JOIN Seq AS s ON m.Val = s.v
)
SELECT *
FROM Grp;

Output:

Id	Val	grp
1	5	0
2	2	0
3	6	0
4	8	0
5	5	4
6	2	4
7	5	6
8	2	6
9	6	6
10	8	6

All we need to do now is to just filter out partial groups:

;WITH Seq AS (
    SELECT v, ROW_NUMBER() OVER(ORDER BY k) AS rn
    FROM (VALUES(1, 5), (2, 2), (3, 6), (4, 8)) x(k,v)
), Grp AS (
SELECT Id, Val, 
       ROW_NUMBER() OVER (ORDER BY Id) - rn AS grp            
FROM mytable AS m
LEFT JOIN Seq AS s ON m.Val = s.v
)
SELECT g1.Id, g1.Val
FROM Grp AS g1
INNER JOIN (
   SELECT grp
   FROM Grp
   GROUP BY grp
   HAVING COUNT(*) = 4 ) AS g2
ON g1.grp = g2.grp

Click here for a live demo.