there is always something to develop
How to count the occurrences of a string in a text
To calculate how many times a specified string occurr in another string, you just need to know the length of original string and a string that you want to count. Replace string you are counting with empty string, calculate the length diffence and divide it by the lenght of the string you are counting. That is the answer...
declare @cText varchar(max); declare @cCountOccurrence varchar(max); set @cText='abc this is abc test abc string'; set @cCountOccurrence = 'abc'; select (len(@cText)-len(replace(@cText,@cCountOccurrence,''))) / len(@cCountOccurrence)
and, for simplier use, as a function:
CREATE FUNCTION [dbo].[StringCountOccurrence]( @cText varchar(max), @cCountOccurrenceOf varchar(max) ) RETURNS int /* WITH ENCRYPTION */ AS BEGIN RETURN ( len(@cText)- len(replace(@cText,@cCountOccurrenceOf,'')) ) / len(@cCountOccurrenceOf) END GO
example of use:
select [dbo].[StringCountOccurrence]( 'abc this is abc test abc string', 'abc')
Comments
"An expert is a person who has made all the mistakes that can be made in a very narrow field."
You are 4683 reader
since 1st April 2009
Yes | 100.0% |
3 |
No | 0 |