CREATE TABLE #StrTbl (
StrID int IDENTITY (1,1) NOT NULL,
Col text NULL
);
INSERT INTO #StrTbl (Col) VALUES ('Blah Blah Blah
blah blah blah
blah blah blah
blah blah blah');
INSERT INTO #StrTbl (Col) VALUES ('Blah Blah Blah
blah blah blah
blah blah blah
blah blah blah');
INSERT INTO #StrTbl (Col) VALUES ('Blah Blah Blah
blah blah blah
blah blah blah
blah blah blah');
-- Sample Output:
SELECT * FROM #StrTbl;
/*
StrID Col
------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Blah Blah Blah blah blah blah blah blah blah blah blah blah
2 Blah Blah Blah blah blah blah blah blah blah blah blah blah
3 Blah Blah Blah blah blah blah blah blah blah 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 where n can range from 1 to 9 for instance.
The primary portion of the tag is the string & the secondary portion
is the string. We have to count how many times appears in a string &
also the number of times occurs.
Consider the tags "" & "". This appears in the row with StrID = 3.
Hence the count for will be 1 & the count for 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 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 , '%%' AS PToken , '%%' AS L1 , '%%' AS L2
UNION ALL
SELECT 2, '%%' , '%%' , '%%'
UNION ALL
SELECT 3, '%%' , '%%' , '%%'
UNION ALL
SELECT 4, '%%' , '%%' , '%%'
) 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
*/
This page was last updated on May 01, 2006 04:28 PM.