MySQL Substr count
Posted: July 17th, 2009 | Author: Rick | Filed under: MySQL | 8 Comments »
Recently I needed to create an SQL statement which was to count the occurences of a character inside a particular database field. For example, you could store the tags connected to a single db-record in a semi-colon (;) separated column in the record. To get the number of tags for this record straight from the database, we would thus like the number of semi-colons in the field (+1) as an indicator for this count. Since MySQL5 does not have a standard function for this (similar to PHP’s substr_count()), I had to find something of my own.
I found the following nifty solution floating around several forums:
SELECT
(LENGTH(`_field_`) - LENGTH(REPLACE(`_field_`,"_substr_", ""))) / LENGTH("_substr_")
AS "iCount"
FROM ...
The trick is straightforward: just remove your separator from the field-contents and see how much smaller the resulting value is compared to the original. Divide this by the length of your separator if it is composed of more than one character. I recommend replacing LENGTH("_substr_") by a number, as you generally know the length of the separator. Leave the division out when using a single length separator:
(LENGTH(`_field_`)-LENGTH(REPLACE(`_field_`,"_substr_", "")))
Posted: July 17th, 2009 | Author: Rick | Filed under: MySQL | 8 Comments »
Recently I needed to create an SQL statement which was to count the occurences of a character inside a particular database field. For example, you could store the tags connected to a single db-record in a semi-colon (;) separated column in the record. To get the number of tags for this record straight from the database, we would thus like the number of semi-colons in the field (+1) as an indicator for this count. Since MySQL5 does not have a standard function for this (similar to PHP’s substr_count()), I had to find something of my own.
I found the following nifty solution floating around several forums:
SELECT
(LENGTH(`_field_`) - LENGTH(REPLACE(`_field_`,"_substr_", ""))) / LENGTH("_substr_")
AS "iCount"
FROM ...
The trick is straightforward: just remove your separator from the field-contents and see how much smaller the resulting value is compared to the original. Divide this by the length of your separator if it is composed of more than one character. I recommend replacing LENGTH("_substr_") by a number, as you generally know the length of the separator. Leave the division out when using a single length separator:
(LENGTH(`_field_`)-LENGTH(REPLACE(`_field_`,"_substr_", "")))
This method may seem a little brute, but it reportedly not as slow as you might think (though I have not benchmarked it).
例子
mysql> SELECT
-> (LENGTH('abcdabcdabcdabcdabcd') - LENGTH(REPLACE('abcdabcdabcdabcdabcd',"abc", ""))) / LENGTH("abc")
-> AS "iCount";
+--------+
| iCount |
+--------+
| 5.0000 |
+--------+
1 row in set (0.00 sec)