MS SQL Tips & Tricks

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')

See also


Learn more tricks

Add Comment

Comments