July 21, 2002 at 4:31 am
Hi !
Can anyone give me any pointers on how to count the number of instances one string appears within another string ?
Thanks !!
Neil
July 21, 2002 at 6:59 am
Steve Jones has a series of articles on using strings in TSQL, you should try those, he probably has some ideas. One way would be replace all the search text with an empty string in a copy of the original string. Substract the length of the new string from the original, divide that by the length of the string you searched for.
Andy
January 9, 2005 at 8:18 pm
This will not work for finding EXACT matches (i.e. Profit and profitablilty)
It finds "profit" twice.
How can we have the instance count be "1" and not count "profit" inside of profitability?
January 10, 2005 at 1:23 am
You could try searching for " profit "
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 10, 2005 at 2:57 am
See, if this helps:
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=153641
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 12, 2005 at 8:17 am
Guys,
Here is a script that I wrote that will fulfill your needs, the output is as follows:
Text Count
<P>Famous for its rive... 3
John McG.
CREATE TABLE #TextSearch (
[Text] [varchar] (3000) NULL ,
[Count] [int] NULL
)
DECLARE @String varchar(1000), @Len int,
@MatchStr varchar(50), @StartIndex int,
@Pos int, @Chunk varchar(50),
@Count int
DECLARE cur_FindString CURSOR FAST_FORWARD FOR SELECT body FROM pagecontent
OPEN cur_FindString FETCH NEXT FROM cur_FindString INTO @STRING
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Pos = 0
SET @Len = LEN(@String)
SET @Count = 0
WHILE CHARINDEX('www', @STRING, @Pos) > 0
BEGIN
SET @StartIndex = CHARINDEX('www', @STRING, @Pos)
IF @StartIndex > 0
BEGIN
INSERT INTO #TextSearch ( [Text])
VALUES (@String)
SET @Count = @Count + 1
END
UPDATE #TextSearch SET [COUNT] = @Count WHERE [Text] = @String
SET @Pos = @StartIndex +4
SET @MatchStr = ''
END
FETCH NEXT FROM cur_FindString INTO @STRING
END
CLOSE cur_FindString
DEALLOCATE cur_FindString
SELECT * FROM #TextSearch
DROP TABLE #TextSearch
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply