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

Learn more tricks

Add Comment

Comments

30th August 2021

https://buysildenshop.com/ - Viagra

Waisuts

8th September 2021

Kamagra Jelly Potenz

Stromectol

16th September 2021

Viagra Comment Marche

Plaquenil

18th September 2021

https://buylasixshop.com/ - Lasix

nertoorne

20th September 2021

Priligy

Luhteerge