Recipe6.3.Counting the Occurrences of a Character in a String

Recipe 6.3. Counting the Occurrences of a Character in a String

Problem

You want to count the number of times a character or substring occurs within a given string. Consider the following string:

 10,CLARK,MANAGER 

You want to determine how many commas are in the string.

Solution

Subtract the length of the string without the commas from the original length of the string to determine the number of commas in the string. Each DBMS provides functions for obtaining the length of a string and removing characters from a string. In most cases, these functions are LENGTH and REPLACE, respectively (SQL Server users will use the built-in function LEN rather than LENGTH):

 1 select (length('10,CLARK,MANAGER')- 2        length(replace('10,CLARK,MANAGER',',','')))/length(',') 3        as cnt 4   from t1 

Discussion

You arrive at the solution by using simple subtraction. The call to LENGTH on line 1 returns the original size of the string, and the first call to LENGTH on line 2 returns the size of the string without the commas, which are removed by REPLACE.

By subtracting the two lengths you obtain the difference in terms of characters, which is the number of commas in the string. The last operation divides the difference by the length of your search string. This division is necessary if the string you are looking for has a length greater than 1. In the following example, counting the occurrence of "LL" in the string "HELLO HELLO" without dividing will return an incorrect result:

  select        (length('HELLO HELLO')-        length(replace('HELLO HELLO','LL','')))/length('LL')        as correct_cnt,        (length('HELLO HELLO')-        length(replace('HELLO HELLO','LL',''))) as incorrect_cnt   from t1 CORRECT_CNT INCORRECT_CNT ----------- -------------           2             4