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 HowManyOccurances
Contributed by: --SQLDenis 02:57, 31 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section Handy tricks
