RE: How can I count the number of occurances of a string in an ntext column for each record? – SQLServerCentral

SSC Guru

Points: 186127

October 1, 2012 at 5:56 am

This will get you 1 and 2:

DROP TABLE #tblHTML

CREATE TABLE #tblHTML (RowID INT, strPage ntext)

INSERT INTO #tblHTML (RowID, strPage)

SELECT

1, REPLICATE(CAST('I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ' AS VARCHAR(MAX)),25)

+ '<table>' + 'I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ' + '</table>'

+ REPLICATE('I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ',25)

+ '<table>' + 'I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ' + '</table>'

+ REPLICATE('I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ',25)

+ '<table>' + 'I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ' + '</table>'

+ REPLICATE('I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ',25)

+ '<table>' + 'I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ' + '</table>'

+ REPLICATE('I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ',25)

+ '<table>' + 'I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ' + '</table>'

+ REPLICATE('I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ',25)

+ '<table>' + 'I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ' + '</table>'

UNION ALL

SELECT

2, REPLICATE(CAST('I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ' AS VARCHAR(MAX)),25)

+ '<table>' + 'I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ' + '</table>'

+ REPLICATE('I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ',25)

+ '<table>' + 'I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ' + '</table>'

+ REPLICATE('I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ',25)

+ '<table>' + 'I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ' + 'xxxxxxxx'

+ REPLICATE('I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ',25)

+ '<table>' + 'I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ' + '</table>'

+ REPLICATE('I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ',25)

+ '<table>' + 'I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ' + '</table>'

+ REPLICATE('I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ',25)

+ '<table>' + 'I have a table [tblHTML] with an ntext column [strPage] that has HTML in it ' + '</table>'

SELECT *, LEN(CAST(strPage AS VARCHAR(MAX))) FROM #tblHTML

;WITH rCTE AS (

SELECT RowID, q.strPage,

lastStartpos = NULLIF(CHARINDEX('<table>',q.strPage,1),0),

lastEndpospos = NULLIF(CHARINDEX('</table>',q.strPage,1),0)

FROM #tblHTML

CROSS APPLY (SELECT strPage = CAST(strPage AS VARCHAR(MAX))) q

UNION ALL

SELECT RowID, strPage,

lastStartpos = NULLIF(CHARINDEX('<table>',strPage,lastStartpos+1),0),

lastEndpospos = NULLIF(CHARINDEX('</table>',strPage,lastEndpospos+1),0)

FROM rCTE

WHERE NOT (lastStartpos IS NULL OR lastEndpospos IS NULL)

)

SELECT RowID, TagStarts = COUNT(lastStartpos), TagEnds = COUNT(lastEndpospos)

FROM rCTE

GROUP BY RowID

HAVING COUNT(lastStartpos) <> COUNT(lastEndpospos)

ORDER BY RowID

OPTION(MAXRECURSION 0)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden