Counting the number of times a character appears in a string

Counting the number of times a character appears in a string

contexts
Counting the number of times a particular character appears in a string is a common requirement in database operations.
Whether you are analyzing text data, formatting strings, or ensuring data consistency, character occurrence counting is an important task for developers and database administrators.
This problem may seem simple, but it can be solved efficiently by the built-in functions of a database management system, avoiding complex loops or manual counting.
Both MySQL and SQL Server provide easy ways to quickly implement character statistics through a combination of string length functions and substitution operations.

MySQL Implementation of Character Statistics
In MySQL, you can use the LENGTH() function to get the length of a string, then use the REPLACE() function to replace the target character with a null, and then calculate the length of the replaced string. Subtract the two to get the number of occurrences of the target character.

SELECT LENGTH('abc-abc-abc') - LENGTH(REPLACE('abc-abc-abc', 'a', '')) AS 'count';

in the end

+---------------------------------------------------------+
| count                                                   |
+---------------------------------------------------------+
| 3                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Explanation:
LENGTH('abc-abc-abc') returns the total length of the string, which is 11 characters.
REPLACE('abc-abc-abc', 'a', '') replaces the letter 'a' in the string with a null character, resulting in "bc-bc-bc", which is 8 long.
Subtracting the two results in the number of occurrences of the character 'a': 11 - 8 = 3.

SQL Server Character Statistics
In SQL Server, you can also use the LEN() function and REPLACE() function to accomplish the same operation. Note that the LEN() function in SQL Server calculates the length of a string without including the trailing space.

SELECT LEN('abc-abc-abc') - LEN(REPLACE('abc-abc-abc', 'a', '')) AS 'count';

in the end

count
-----
3
​

Explanation:
LEN('abc-abc-abc') Returns a string of length 11.
REPLACE('abc-abc-abc', 'a', '') replaces all 'a' with null, leaving the string "bc-bc-bc" of length 8.
Subtracting the two gives the number of occurrences of the character 'a': 11 - 8 = 3.

summarize
Whether it's MySQL or SQL Server, counting the number of times a character appears in a string is very simple. By utilizing the difference in length of the strings, we can get quick results.

For those scenarios where character counting needs to be done efficiently, this approach is certainly fast and practical.
Users can apply this method to more complex queries according to actual needs, or even encapsulate it as a function for future use.


reference article
/
/cn/industries/commercial-market/active-active-data-center-solution
/374/
/hjx020/article/details/106588133/

This article is copyrighted and may not be reproduced without the author's permission.