You wish to determine how many times a particular character or substring appears in a given string. Consider the following string:
10,CLARK,MANAGER
To find the number of commas in a string, subtract the length of the string with the commas removed from the original length of the string. Most DBMSs have built-in functions for finding the length of a string (such as LENGTH or LEN) and removing characters from a string (such as REPLACE). These functions can be used to calculate the number of commas in the string.
SELECT (length('10,CLARK,MANAGER')-
length(replace('10,CLARK,MANAGER',',','')))/length(',')
AS cnt
| cnt |
|---|
| 2 |
Discussion
To find the number of commas in a string, the length of the string with the commas removed is subtracted from the original length of the string. This difference is then divided by the length of the search string (if it is greater than 1) to get the final answer. This process uses the built-in functions LENGTH or LEN to find the length of the string and REPLACE to remove the commas.
Code Runner
Copy the below query in the code runner to see the result
SELECT (length('10,CLARK,MANAGER')-
length(replace('10,CLARK,MANAGER',',','')))/length(',')
AS cnt