This appears to work:
I've elected to show rows where both values are zero in case these are wrong - You can remove this if you want.
I've set MAXRECURSION to 10, but you can increase this if necessary...
IF OBJECT_ID('dbo.tblHTML') IS NOT NULL
DROP TABLE dbo.tblHTML;
CREATE TABLE [dbo].[tblHTML](
[RowId] [int] NOT NULL,
[strPage] [ntext] NULL
) ON [PRIMARY]
insert into dbo.tblHTML values ( 1, '<table>www</table><table>xxx</table><table>yyy</table>zzz</table>' );
insert into dbo.tblHTML values ( 2, '<table>xxx</table><table>yyy</table><table>zzz</table>' );
insert into dbo.tblHTML values ( 3, '>xxx</table><table>yyy</table><table>zzz</table>' );
insert into dbo.tblHTML values ( 4, '>xxx</table>' );
insert into dbo.tblHTML values ( 5, '>xxx</table' );
insert into dbo.tblHTML values ( 6, '<table>xxx</table>' );
DECLARE @SearchHTMLStart Varchar(30), @SearchHTMLEnd Varchar(30);
SET @SearchHTMLStart = '<table>';
SET @SearchHTMLEnd = '</table>';
WITH CTE_Start AS
(
SELECT RowId,
Occurrence = CASE WHEN CHARINDEX ( @SearchHTMLStart ,[strPage] , 0 ) > 0 THEN 1 ELSE 0 END,
Location = CHARINDEX ( @SearchHTMLStart ,[strPage] , 0 )
FROM dbo.tblHTML
UNION ALL
SELECT h.RowId,
Occurrence = CASE WHEN CHARINDEX ( @SearchHTMLStart ,h.[strPage] , c.Location +1 ) > 0 THEN 1 ELSE 0 END,
Location = CHARINDEX ( @SearchHTMLStart ,h.[strPage] , c.Location +1 )
FROM dbo.tblHTML h
INNER JOIN CTE_Start c ON h.RowId = c.RowId
WHERE Occurrence > 0
),
CTE_End AS
(
SELECT RowId,
Occurrence = CASE WHEN CHARINDEX ( @SearchHTMLEnd ,[strPage] , 0 ) > 0 THEN 1 ELSE 0 END,
Location = CHARINDEX ( @SearchHTMLEnd ,[strPage] , 0 )
FROM dbo.tblHTML
UNION ALL
SELECT h.RowId,
Occurrence = CASE WHEN CHARINDEX ( @SearchHTMLEnd ,h.[strPage] , c.Location +1 ) > 0 THEN 1 ELSE 0 END,
Location = CHARINDEX ( @SearchHTMLEnd ,h.[strPage] , c.Location +1 )
FROM dbo.tblHTML h
INNER JOIN CTE_End c ON h.RowId = c.RowId
WHERE Occurrence > 0
),
CTE_Start_Sum AS
(
SELECT s.RowId, SUM(s.Occurrence) as NumberStart
FROM CTE_Start s
GROUP BY s.RowId
),
CTE_End_Sum AS
(
SELECT e.RowId, SUM(e.Occurrence) as NumberEnd
FROM CTE_End e
GROUP BY e.RowId
)
SELECT ISNULL(s.RowId, e.RowId) as RowId, NumberStart, NumberEnd
FROM CTE_Start_Sum s
INNER JOIN CTE_End_Sum e ON s.RowId = e.RowId
WHERE (NumberStart <> NumberEnd) OR (NumberStart = 0 AND NumberEnd = 0)
OPTION (MAXRECURSION 10);