Binaryworld - Counting ocurrences of a pattern in text data ... [ SQL Server -> T-SQL ]

A sample script to count the number of occurrences of a particular string pattern in a text column. This demonstrates how to use PATINDEX with simple SELECT statements.

Click here to copy the following block

CREATE TABLE #StrTbl (
StrID int IDENTITY (1,1) NOT NULL,
Col text NULL
);

INSERT INTO #StrTbl (Col) VALUES ('Blah Blah Blah
<P1><L1> blah blah blah
<P2><L2> blah blah blah
<P3><L1> blah blah blah');

INSERT INTO #StrTbl (Col) VALUES ('Blah Blah Blah
<P2><L1> blah blah blah
<P1><L1> blah blah blah
<P4><L2> blah blah blah');

INSERT INTO #StrTbl (Col) VALUES ('Blah Blah Blah
<P1><L1> blah blah blah
<P1><L2> blah blah blah
<P3><L2> blah blah blah');
-- Sample Output:
SELECT * FROM #StrTbl;
/*
StrID Col
------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Blah Blah Blah <P1><L1> blah blah blah <P2><L2> blah blah blah <P3><L1> blah blah blah
2 Blah Blah Blah <P2><L1> blah blah blah <P1><L1> blah blah blah <P4><L2> blah blah blah
3 Blah Blah Blah <P1><L1> blah blah blah <P1><L2> blah blah blah <P3><L2> blah blah blah
*/
GO
-- Objective:
/*
    To count the patterns of each tag that appears in the string.
    For instance , the query should give the count of each pair of tags.
    The tags are found as <Pn><Ln> where n can range from 1 to 9 for instance.
    The primary portion of the tag is the string <Pn> & the secondary portion
    is the <Ln> string. We have to count how many times <Pn> appears in a string &
    also the number of times <Ln> occurs.
    Consider the tags "<P1><L1>" & "<P1><L2>". This appears in the row with StrID = 3.
    Hence the count for <Pn> will be 1 & the count for <Ln> will be 2.
    The query below gives the expected results & it will work on text columns also
    because of the use of the PATINDEX function.
*/
SELECT StrID , Seq AS P , SUM( L1 + L2 ) AS LCount
FROM
(
SELECT StrID , Seq,
    -- Use PATINDEX to get the count for each tag <Ln> in the matching strings
    -- We will add these together later.
CASE WHEN PATINDEX( L1 , Col ) > 0 THEN 1 ELSE 0 END AS L1 ,
CASE WHEN PATINDEX( L2 , Col ) > 0 THEN 1 ELSE 0 END AS L2
FROM #StrTbl
JOIN
(
/*
    First generate a virtual table that contains all possible tags.
    This can come from a permanent table also. The columns for the
    virtual table are a seq# , token to search for, the combinations of it.
    We restrict to 2 in this case.
*/
SELECT 1 AS Seq , '%<P1>%' AS PToken , '%<P1><L1>%' AS L1 , '%<P1><L2>%' AS L2
UNION ALL
SELECT 2, '%<P2>%' , '%<P2><L1>%' , '%<P2><L2>%'
UNION ALL
SELECT 3, '%<P3>%' , '%<P3><L1>%' , '%<P3><L2>%'
UNION ALL
SELECT 4, '%<P4>%' , '%<P4><L1>%' , '%<P4><L2>%'
) AS t
-- Search for the token in the original string
ON PATINDEX( pToken , Col ) > 0
) AS t2
GROUP BY StrID , Seq
ORDER BY StrID , Seq;
/*
StrID    P      LCount
----------- ----------- -----------
     1      1      1
     1      2      1
     1      3      1
     2      1      1
     2      2      1
     2      4      1
     3      1      2
     3      3      1
*/