Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

Find Out How Many Occurrences Of A Substring Are In A String

From Wiki

Jump to: navigation, search

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

  1. DECLARE @chvString VARCHAR(500)
  2.     SELECT @chvString ='ababababajdfgrhgjrhgierghierabababaaaaaaaabbbbbbbaaaa'
  3.      
  4.     DECLARE @chvSearchString VARCHAR(50)
  5.     SELECT @chvSearchString = 'ab'
  6.      
  7.     SELECT LEN(@chvString) AS StringLength,
  8.     LEN(@chvSearchString) AS SearchForStringLength,
  9.     (LEN(@chvString)-
  10.     (LEN(REPLACE(@chvString,@chvSearchString,''))))/LEN(@chvSearchString)
  11.      AS HowManyOccurances


Contributed by: --SQLDenis 02:57, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Handy tricks

180 Rating: 2.3/5 (3 votes cast)