laurie-789651 (10/1/2012)

---

laurie-789651 (10/1/2012)

---

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);

I'm informed it won't work properly:

CHARINDEX won't work properly with ntext, you have to cast to VARCHAR(max), otherwise it stops working at 8000 characters

Thanks ChrisM@Work 🙂