Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can I count the number of occurances of a string in an ntext column for each record? Expand / Collapse
Author
Message
Posted Sunday, September 30, 2012 11:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 3, 2014 3:18 PM
Points: 2, Visits: 104
Dear 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 ( like this one) but those do not seem to work because "Argument data type ntext is invalid for argument 1 of replace function."

Then I found a post here at SQL Sever Central 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!
Post #1366321
Posted Monday, October 1, 2012 4:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 2,873, Visits: 5,190
You should really avoid using ntext, text and image datatypes and use nvarchar(max), varchar(max), and varbinary(max) instead http://msdn.microsoft.com/en-us/library/ms187993.aspx. 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.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1366457
Posted Monday, October 1, 2012 5:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 6,872, Visits: 14,185
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1366504
Posted Monday, October 1, 2012 6:12 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 318, Visits: 1,085
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);


Post #1366511
Posted Monday, October 1, 2012 6:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 12,927, Visits: 32,331
i like all the examples so rar, but make sure you search for <table without the ending > tag.
if the table has a class, a css style or even older style html, you will get misleading counts;
<table border="1">

<table class="main_css">


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1366514
Posted Monday, October 1, 2012 6:24 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 318, Visits: 1,085
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
Post #1366516
Posted Monday, October 1, 2012 6:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 6,872, Visits: 14,185
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


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?


“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
Exploring Recursive CTEs by Example Dwain Camps
Post #1366519
Posted Monday, October 1, 2012 7:55 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 318, Visits: 1,085
Solution incorporating the above suggestions (thank you):


--====== 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.tblHTML
CROSS APPLY (SELECT strPage = CAST(strPage AS VARCHAR(MAX))) q
UNION ALL
SELECT 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 h
CROSS APPLY (SELECT strPage = CAST(strPage AS VARCHAR(MAX))) q
INNER JOIN CTE_Start c ON h.RowId = c.RowId
WHERE 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.tblHTML
CROSS APPLY (SELECT strPage = CAST(strPage AS VARCHAR(MAX))) q
UNION ALL
SELECT 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 h
CROSS APPLY (SELECT strPage = CAST(strPage AS VARCHAR(MAX))) q
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);


Post #1366578
Posted Monday, October 1, 2012 12:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 3, 2014 3:18 PM
Points: 2, Visits: 104
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 <table 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!

Thank you all again!
Post #1366701
Posted Tuesday, October 2, 2012 2:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 6,872, Visits: 14,185
Mixolydian (10/1/2012)
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 <table 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!

Thank you all again!


You're welcome, thanks for the feedback. If you have trouble understanding the solutions posted, just ask


“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
Exploring Recursive CTEs by Example Dwain Camps
Post #1366891
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse