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

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table