Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Find Out How Many Occurrences Of A Substring Are In A String
From Wiki
If you want to know how many occurrences of a substring you have in a string play around with the code below. The trick here is to take the total length of the string, subtract the same string minus the occurrences and divide by the length of the substring
For example, String is ’ABABABABZZZZZ’, length is 13 Substring is ’AB’ length is 2 Length of string minus all occurrences of substring is 5 So (13 -5) /2 =4 occurrences
- DECLARE @chvString VARCHAR(500)
- SELECT @chvString ='ababababajdfgrhgjrhgierghierabababaaaaaaaabbbbbbbaaaa'
- DECLARE @chvSearchString VARCHAR(50)
- SELECT @chvSearchString = 'ab'
- SELECT LEN(@chvString) AS StringLength,
- LEN(@chvSearchString) AS SearchForStringLength,
- (LEN(@chvString)-
- (LEN(REPLACE(@chvString,@chvSearchString,''))))/LEN(@chvSearchString)
- AS HowManyOccurrences
This thread ASP Thread may be relevant for this discussion.
Contributed by: --SQLDenis 02:57, 31 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section Handy tricks
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.