InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 16:09:39 GMT20http://www.sqlservercentral.com/Forums/Topic1366321-1292-1.aspx[quote][b]Mixolydian (10/1/2012)[/b][hr]Wow! I had hoped to find a response but I am overwhelmed. Thank you all very much!Lowell, thank you for the valuable tip. I do need to check for [b]<table[/b] in case there are some classes or other values in the opening tag.SSCommitted, You are quite right, this database has been around a while. Thank you for the suggestion I will read up on changing the data type.And ChrisM@Work & laurie-789651, thank you both for your code! Both your examples are way over my head, but I'm going to pick them apart and learn how they work. I've already run the query (with the <table modification) and found the 500 or so records out of 30,000 which need fixing. I'm very happy! :w00t:Thank you all again! :-D[/quote]You're welcome, thanks for the feedback. If you have trouble understanding the solutions posted, just ask ;-)Tue, 02 Oct 2012 02:05:38 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1366321-1292-1.aspxWow! I had hoped to find a response but I am overwhelmed. Thank you all very much!Lowell, thank you for the valuable tip. I do need to check for [b]<table[/b] in case there are some classes or other values in the opening tag.SSCommitted, You are quite right, this database has been around a while. Thank you for the suggestion I will read up on changing the data type.And ChrisM@Work & laurie-789651, thank you both for your code! Both your examples are way over my head, but I'm going to pick them apart and learn how they work. I've already run the query (with the <table modification) and found the 500 or so records out of 30,000 which need fixing. I'm very happy! :w00t:Thank you all again! :-DMon, 01 Oct 2012 12:33:41 GMTMixolydianhttp://www.sqlservercentral.com/Forums/Topic1366321-1292-1.aspxSolution incorporating the above suggestions (thank you):[code="sql"]--====== TEST DATA =============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>' );--========= SOLUTION Altered as recommended by ChrisM@Work ===============DECLARE @SearchHTMLStart Varchar(30), @SearchHTMLEnd Varchar(30);SET @SearchHTMLStart = '<table'; -- Leaving off trailing '>'SET @SearchHTMLEnd = '</table>';WITH CTE_Start AS(SELECT RowId, Occurrence = CASE WHEN CHARINDEX ( @SearchHTMLStart ,q.[strPage] , 0 ) > 0 THEN 1 ELSE 0 END, Location = CHARINDEX ( @SearchHTMLStart ,q.[strPage] , 0 )FROM dbo.tblHTMLCROSS APPLY (SELECT strPage = CAST(strPage AS VARCHAR(MAX))) qUNION ALLSELECT h.RowId, Occurrence = CASE WHEN CHARINDEX ( @SearchHTMLStart ,q.[strPage] , c.Location +1 ) > 0 THEN 1 ELSE 0 END, Location = CHARINDEX ( @SearchHTMLStart ,q.[strPage] , c.Location +1 )FROM dbo.tblHTML hCROSS APPLY (SELECT strPage = CAST(strPage AS VARCHAR(MAX))) q INNER JOIN CTE_Start c ON h.RowId = c.RowIdWHERE Occurrence > 0),CTE_End AS(SELECT RowId, Occurrence = CASE WHEN CHARINDEX ( @SearchHTMLEnd ,q.[strPage] , 0 ) > 0 THEN 1 ELSE 0 END, Location = CHARINDEX ( @SearchHTMLEnd ,q.[strPage] , 0 )FROM dbo.tblHTMLCROSS APPLY (SELECT strPage = CAST(strPage AS VARCHAR(MAX))) qUNION ALLSELECT h.RowId, Occurrence = CASE WHEN CHARINDEX ( @SearchHTMLEnd ,q.[strPage] , c.Location +1 ) > 0 THEN 1 ELSE 0 END, Location = CHARINDEX ( @SearchHTMLEnd ,q.[strPage] , c.Location +1 )FROM dbo.tblHTML hCROSS APPLY (SELECT strPage = CAST(strPage AS VARCHAR(MAX))) q INNER JOIN CTE_End c ON h.RowId = c.RowIdWHERE Occurrence > 0),CTE_Start_Sum AS(SELECT s.RowId, SUM(s.Occurrence) as NumberStartFROM CTE_Start sGROUP BY s.RowId),CTE_End_Sum AS(SELECT e.RowId, SUM(e.Occurrence) as NumberEndFROM CTE_End eGROUP 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.RowIdWHERE (NumberStart <> NumberEnd) OR (NumberStart = 0 AND NumberEnd = 0)OPTION (MAXRECURSION 10);[/code]Mon, 01 Oct 2012 07:55:26 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1366321-1292-1.aspx[quote][b]laurie-789651 (10/1/2012)[/b][hr][quote][b]laurie-789651 (10/1/2012)[/b][hr]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...[code="sql"]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.tblHTMLUNION ALLSELECT 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.RowIdWHERE 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.tblHTMLUNION ALLSELECT 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.RowIdWHERE Occurrence > 0),CTE_Start_Sum AS(SELECT s.RowId, SUM(s.Occurrence) as NumberStartFROM CTE_Start sGROUP BY s.RowId),CTE_End_Sum AS(SELECT e.RowId, SUM(e.Occurrence) as NumberEndFROM CTE_End eGROUP 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.RowIdWHERE (NumberStart <> NumberEnd) OR (NumberStart = 0 AND NumberEnd = 0)OPTION (MAXRECURSION 10);[/code][/quote]I'm informed it won't work properly:[quote]CHARINDEX won't work properly with ntext, you have to cast to VARCHAR(max), otherwise it stops working at 8000 characters[/quote]Thanks ChrisM@Work :-)[/quote]You're welcome - your query works just fine if you introduce the CAST where necessary. Why am I writing this when you're sitting 3 feet away? How was your weekend? :-DMon, 01 Oct 2012 06:29:39 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1366321-1292-1.aspx[quote][b]laurie-789651 (10/1/2012)[/b][hr]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...[code="sql"]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.tblHTMLUNION ALLSELECT 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.RowIdWHERE 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.tblHTMLUNION ALLSELECT 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.RowIdWHERE Occurrence > 0),CTE_Start_Sum AS(SELECT s.RowId, SUM(s.Occurrence) as NumberStartFROM CTE_Start sGROUP BY s.RowId),CTE_End_Sum AS(SELECT e.RowId, SUM(e.Occurrence) as NumberEndFROM CTE_End eGROUP 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.RowIdWHERE (NumberStart <> NumberEnd) OR (NumberStart = 0 AND NumberEnd = 0)OPTION (MAXRECURSION 10);[/code][/quote]I'm informed it won't work properly:[quote]CHARINDEX won't work properly with ntext, you have to cast to VARCHAR(max), otherwise it stops working at 8000 characters[/quote]Thanks ChrisM@Work :-)Mon, 01 Oct 2012 06:24:02 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1366321-1292-1.aspxi like all the examples so rar, but make sure you search for [b]<table [/b]without the ending > tag.if the table has a class, a css style or even older style html, you will get misleading counts;[code="plain"]<table border="1"><table class="main_css">[/code]Mon, 01 Oct 2012 06:19:46 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1366321-1292-1.aspxThis 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...[code="sql"]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.tblHTMLUNION ALLSELECT 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.RowIdWHERE 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.tblHTMLUNION ALLSELECT 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.RowIdWHERE Occurrence > 0),CTE_Start_Sum AS(SELECT s.RowId, SUM(s.Occurrence) as NumberStartFROM CTE_Start sGROUP BY s.RowId),CTE_End_Sum AS(SELECT e.RowId, SUM(e.Occurrence) as NumberEndFROM CTE_End eGROUP 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.RowIdWHERE (NumberStart <> NumberEnd) OR (NumberStart = 0 AND NumberEnd = 0)OPTION (MAXRECURSION 10);[/code]Mon, 01 Oct 2012 06:12:15 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1366321-1292-1.aspxThis will get you 1 and 2:[code="sql"]DROP TABLE #tblHTMLCREATE 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 ALLSELECT 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 rCTEGROUP BY RowIDHAVING COUNT(lastStartpos) <> COUNT(lastEndpospos) ORDER BY RowIDOPTION(MAXRECURSION 0)[/code]Mon, 01 Oct 2012 05:56:47 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1366321-1292-1.aspxYou should really avoid using ntext, text and image datatypes and use nvarchar(max), varchar(max), and varbinary(max) instead [url]http://msdn.microsoft.com/en-us/library/ms187993.aspx[/url]. I guess you have old database migrated from previous version of SQL Server.For mow you can convert ntext to nvarchar(max) before using string functions. However, I would create and use CLR function which would give way better performance for what you're trying to do.Mon, 01 Oct 2012 04:05:29 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1366321-1292-1.aspxDear SQL Gurus:I have a table [tblHTML] with an ntext column [strPage] that has HTML in it.Each record's [strPage] field text should contain an equal number of '<table>' and '</table>' strings. But some do not. I need to find those records so I can fix them.I think what I need to do is: 1. figure out how many occurrences of '<table>' and '</table>' there are in each record's [strPage].2. If the number of '<table>' strings does not equal the number of '</table>' strings, then return the id of that record as part of the recordset.I'm stuck on #1. I've looked for 3 hours now, and I haven't found anyone else with my exact issue - to return a count for each record of the number of occurrences of a string in an ntext field. I found some solutions that involve using LEN and REPLACE ( [url="http://www.sqlmag.com/content1/topic/counting-instances-of-a-word-in-a-record/catpath/tsql3/page/2"]like this one[/url]) but those do not seem to work because "Argument data type ntext is invalid for argument 1 of replace function."Then I found [url="http://www.sqlservercentral.com/Forums/Topic358246-8-1.aspx"]a post here at SQL Sever Central[/url] and I eagerly made a number table to use with it, but then I realized it returns a total count for the column, not the individual records that contain the string.Can you please help me or point me in the right direction? Thank you!Sun, 30 Sep 2012 23:31:53 GMTMixolydian