MySQL: Count Occurances Of A String Using REPLACE and LEN Functions

The MySQL command "REPLACE" may be used to determine how many times a character or string occurs in a larger text string.

SQL allows the user to count how many times a character occurs within a larger string. The process used to do this can be extended to count occurrences of a substring, with some minor changes. The SQL commands REPLACE and LEN are used.

The basic algorithm used can be extended to other languages or software, with some changes to the syntax. (The SQL function LEN and LENGTH mean the same, depending on the SQL version being used).

SQL Count Instances of a Character in a String - Algorithm

An example will be used here to illustrate the use of the REPLACE and LEN functions: "Count how many times the character "d" occurs in the string "Edward Woodward". Clearly, the answer is 4.

The length of the string "Edward Woodward" is 15. (6 for Edward, 1 for the space, and 8 for Woodward). If all instances of the letter "d" are removed, to give "Ewar Woowar", then the length of the string decreases by 4 to 11.

If the letter "o" was the one the user was interested in, then the string would decrease in length by 2, since there are two instances of the character "o". The letter "w" is slightly different, since there are two lower case "w" instances, and one upper case instance. This can also be accounted for, and will be explained later.

SQL Count Instances of a Character in a String - Syntax

Using the example of counting instances of the character "d" in the string "Edward Woodward", the user is really asking:

What is the length of the string "Edward Woodward" minus the length of "Edward Woodward" with the "d" characters all taken away? In SQL, the first part is simply

LEN("Edward Woodward")

The second part uses the REPLACE function. Basically, the letter "d" is replaced with nothing, or "": To remove the "d" characters, the syntax is

REPLACE("Edward Woodward","d","")

This will return "Ewar Woowar" as desired.

Putting it all together, the full function is then

LEN("Edward Woodward") - LEN(REPLACE("Edward Woodward","d",""))

In the case where it was desired to find all instances of "w", both upper case"W" and lower case "w", then the original string may be changed to all lower case:

LEN("Edward Woodward") - LEN(REPLACE(LOWER("Edward Woodward"),"d",""))
SQL Count Instances of a Substring in a String - Syntax

Going one step further, to find how many times a substring occurs, is also possible. For example, to count how many times the string "ward" occurs in "Edward Woodward", the same basic algorithm is used. Since replacing "ward" in the string reduces the total string length by 4, then the overall function needs to be divided by 4:

(LEN("Edward Woodward") - LEN(REPLACE("Edward Woodward","ward",""))) / LEN("ward")

or

(15 - 7) / 4

= 2

as desired.

SQL Character Count in String - Summary

Counting the number of instances of a substring in a string can be done by counting the length of the string before and after removing the substring. The syntax for both upper and lower cases, and one case only have been described using examples. (The SQL function LEN and LENGTH mean the same, depending on the SQL version being used).

The principle used can be extended to other software types such as Excel, JSL Scripts, Java etc.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---