Vicky Vicky - 28 days ago 9
SQL Question

Query to find records following same sequence of patterns?

How to find records which matches certain sequences from the below table:

ID s_id task
1 1 a
1 2 b
1 3 b
1 4 c
1 5 c
1 6 d
1 7 a
2 1 a
2 2 c
2 3 c
3 1 a
3 2 b
3 3 c
3 4 d
1 1 a
1 2 b
1 3 c
1 4 c
1 5 e
1 6 d


How to fetch the records following the below pattern


  1. a

  2. 1 or more b

  3. c


Answer

Below code will transform your data to a list of site visits (id) followed by a single string of characters representing pages visited (e.g. "abbbcd").

       SELECT t2.id, max(tasks) as tasks from
        (
            SELECT t1.id, 
            (SELECT '' + Task FROM [Table] WHERE id = t1.id FOR XML PATH(''))  AS tasks
            from [Table] t1
        ) t2
        group by t2.id

So the problem is now reduced to searching for a pattern of characters: a--any number of b's--c. You can use LIKE to do this:

SELECT *
FROM (
        SELECT t2.id, max(tasks) as tasks from
        (
            SELECT t1.id, 
            (SELECT '' + Task FROM [Table] WHERE id = t1.id FOR XML PATH(''))  AS tasks
            from [Table] t1
        ) t2
        group by t2.id
    ) t3
WHERE t3.tasks LIKE '%abc%'
 OR t3.tasks LIKE '%abbc%'
  OR t3.tasks LIKE '%abbbc%' 
  OR t3.tasks LIKE '%abbbbc%';

This is a bit crude. You want to say any number of b's, but LIKE does not support that. This is what regular expressions (RegEx) is normally used for. The expression would be "ab+c" which stand for: "a" followed by 1 or more "b"s, followed by a "c".

Unfortunately, SQL server does not support regex (Oracle does), so you have to use CLR to implement it. Others have done this for you, so you can follow instructions here to install it: https://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/