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
*/