Many times an Oracle developer will need to determine the number of occurrences of a string, or phrase, inside a larger string. For example, if you have the string 1,2,3,4 you may need to determine how many commas are in the string. Problems like this are frequently encountered when parsing text and with certain replacement operations.
This is a simple problem to solve procedurally, with a PL/SQL loop. However, performing the count in a stand-alone SQL statement often makes solving your problem easier and brings with it increase performance.
1.We start our solution with the existing oracle
length function. If you have a through understand of this function you can skip to step 2. The length function returns the number of characters in a given string. For example, executing the length function on the string 1,2,3,4 would return 7 because there are 7 characters in the string (4 numerals and 3 commas).
Try it yourself with this command:
select length('1,2,3,4') from dual;
2.The second piece of the solution utilizes the existing oracle
replace function. If you have a through understand of this function you can skip to step 3. The replace function returns a new string with on character of phrase replaced with another. For this problem, we want to replace the comma with nothing. For example, executing the replace function on the string 1,2,3,4 and replace the comma would return 1234.
Try it yourself with the command:
select replace('1,2,3,4',',') from dual;
3.Now you combine the replace and length function to get the length of the string without the commas. For example, the length of the string 1,2,3,4 without the commas is 4.
Try it yourself:
select length(replace('1,2,3,4',',')) from dual;
4.Now you simply subtract the length of the new (replaced) string from that or the original string and you get the count of commas which is 3.
Here is the full SQL. Try it for yourself:
select length('1,2,3,4') - length(replace('1,2,3,4',',')) from dual;
This example explains how to get the count of a single character within a string. It can easily be modified to get the count of a phrase within a string. For example, assume you have the phrase: the sql statement solves the problem and you want to get the count of the word the. You simply change the character to be replaced to the word the and divide the answer by the length of the string to be replaced.
Try it for yourself:
select (length('the sql statement solves the problem') - length(replace('the sql statement solves the problem','the')))/length('the') from dual;
