January 27, 2012 at 9:20 am
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 *******
January 27, 2012 at 9:34 am
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/
January 27, 2012 at 9:46 am
AccidentalDBA:) (1/27/2012)
Hello AllDoes 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.
January 29, 2012 at 12:15 pm
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 *******
January 30, 2012 at 12:05 am
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
Change is inevitable... Change for the better is not.
January 30, 2012 at 2:30 am
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 *******
January 30, 2012 at 10:56 am
And I was going to suggest a SELECT Max(index) from one of Jeff's most excellent split functions
February 15, 2012 at 8:21 pm
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 0declare @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
Change is inevitable... Change for the better is not.
February 15, 2012 at 9:50 pm
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.
February 16, 2012 at 3:35 am
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 *******
February 16, 2012 at 6:53 am
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
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply