Count the number of occurrences of a char in MySQL

Posted on Sunday April 10, 2011 / by Eric Potvin

For some reason, could be good or not, you want to know the number of occurrences of a character (or a string) in a MySQL field or a string.

Let's say we have the following table:

mysql> desc `url`;
+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| id             | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name           | varchar(50)      | NO   |     |         |                |
| url            | varchar(255)     | NO   |     |         |                |
| fk_category_id | int(10) unsigned | NO   |     | NULL    |                |
+----------------+------------------+------+-----+---------+----------------+

We want to find how many slashes "/" each url have. The easy way is using the length and the replace command:

LENGTH([field]) - LENGTH(REPLACE([field], '[char_to_find]', ''))

Here's an simple example:

mysql> select `id`, `url`, LENGTH(`url`) - LENGTH(REPLACE(`url`, '/', '')) as `number` from `url`;
+----+-------------------------------+--------+
| id | url                           | number |
+----+-------------------------------+--------+
|  1 | http://www.thesunrisepost.com |      2 |
+----+-------------------------------+--------+