How to count the occurrences of a character in a string?

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.

Subtract the length of the string without the commas from the original length of thestring 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):

SELECT (length('10,CLARK,MANAGER')-
length(replace('10,CLARK,MANAGER',',','')))/length(',')
AS cnt
cnt
2

Discussion

Simple subtraction is used to get the answer. The second call to LENGTH on line 2 returns the size of the string without the commas, which are eliminated by REPLACE, whereas the initial call to LENGTH on line 1 returns the string’s original size. The difference in terms of characters—which is the number of commas in the string—is obtained by subtraction of the two lengths. 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.