COUNT No of occurrances in a text field

Question

COUNT No of occurrances in a text field

SGT_squeequal

SSCertifiable

Points: 7167
More actions
January 27, 2012 at 9:20 am

#249940

Hello All
Does anyone know how to count the number of occurances of a string within a text field, i have tried
SELECT (DATALENGTH(REPLACE(xml_text, '<TAG', '')) - DATALENGTH(xml_Text) )
FROM xml_TEXT_table
However, i find that replace can not be used on TEXT data types, its not possible to cast as varchar as the TEXT field can be way over 8000 char long HELP anyone 🙂 :w00t:
***The first step is always the hardest *******

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

You can cast to varchar(max). Does that get you in the right direction. If at all possible you should consider changing your datatype to varchar(max) instead of text. The text datatype has been deprecated and will no longer be supported.

http://msdn.microsoft.com/en-us/library/ms187993.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

AccidentalDBA:) (1/27/2012)
Hello All
Does anyone know how to count the number of occurances of a string within a text field, i have tried
SELECT (DATALENGTH(REPLACE(xml_text, '<TAG', '')) - DATALENGTH(xml_Text) )
FROM xml_TEXT_table
However, i find that replace can not be used on TEXT data types, its not possible to cast as varchar as the TEXT field can be way over 8000 char long HELP anyone 🙂 :w00t:

Here's a dog-ugly slow method.

DECLARE @occurance VARCHAR(10) = 'AB'

;WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),

t2(N) AS (SELECT 1 FROM t1 x, t1 y),

t3(N) AS (SELECT 1 FROM t2 x, t2 y),

t4(N) AS (SELECT 1 FROM t3 x, t3 y),

Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM t4 x, t4 y)

SELECT COUNT(*)

FROM (SELECT SUBSTRING(string,N,LEN(@occurance))

FROM Tally

CROSS APPLY (SELECT string

FROM #test

WHERE SUBSTRING(string,N,LEN(@occurance)) = @occurance) a) b(occurance)

Will only work if the "TEXT" data is less than 65,536 characters.

Best bet is to use VARCHAR(MAX) instead of TEXT which has been deprecated.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 3

Thanks guys for all you help, managed to fix my script a differnet way instead of count in numbe of occurances of a specific word for my while loop i compared results of script to stop when the result was 0 and worked fine..

:w00t:

***The first step is always the hardest *******

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

AccidentalDBA:) (1/29/2012)
Thanks guys for all you help, managed to fix my script a differnet way instead of count in numbe of occurances of a specific word for my while loop i compared results of script to stop when the result was 0 and worked fine..
:w00t:

Any chance of you posting your fixed script?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 5

Lo here is what i used, instead of counting the number of occurances to get my while loop to only run that many times it loops untill the value is 0

declare @start int,
@end int,
@st int
set @start =1
set @st=0
while @start !=0
begin
set @start=(select cast (CHARINDEX('<baseshape ',xml_data,@st) as varchar)
from #xml_table)
set @end=(select cast (CHARINDEX('</BaseShape>',xml_data,@start)-@start+12 as varchar)
from #xml_table)
insert into #xml select SUBSTRING (xml_data,@start,@end)
from #xml_table
set @st=@start+10
end

***The first step is always the hardest *******

Mad Myche SSCommitted Points: 1593 More actions · Answer 6

And I was going to suggest a SELECT Max(index) from one of Jeff's most excellent split functions

Director of Transmogrification Services

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

SGT_squeequal (1/30/2012)
Lo here is what i used, instead of counting the number of occurances to get my while loop to only run that many times it loops untill the value is 0
declare @start int,
@end int,
@st int
set @start =1
set @st=0
while @start !=0
begin
set @start=(select cast (CHARINDEX('<baseshape ',xml_data,@st) as varchar)
from #xml_table)
set @end=(select cast (CHARINDEX('</BaseShape>',xml_data,@start)-@start+12 as varchar)
from #xml_table)
insert into #xml select SUBSTRING (xml_data,@start,@end)
from #xml_table
set @st=@start+10
end

I don't know what your entire XML line looks like but wouldn't an XQUERY work faster?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ben Teraberry SSChampion Points: 11845 More actions · Answer 8

If you have a need to improve the performance and you are not overly restricted in your environment, this would be a great place to use CLR. The longer the strings the more this would be true, but with any size strings CLR would be significantly faster.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 9

Never used Xquery, first time i have worked with bringing XML into SQL, the XML can be very large having over 100 sections of drawings.

i will have to have a look at Xquery

Thanks

***The first step is always the hardest *******

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 10

bteraberry (2/15/2012)
If you have a need to improve the performance and you are not overly restricted in your environment, this would be a great place to use CLR. The longer the strings the more this would be true, but with any size strings CLR would be significantly faster.

But not as much as they used to be in the world of 8k strings. 🙂 Have you seen the new delimited splitter I wrote about in the "Tally OH!" article? I was significantly fast than the old Tally based splitter and then a couple of folks in the discussion made it even fast with a tweak.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)