Count the number of occurrences of a char in MySQL

Monday, April 11, 2011 EDT

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 | 
+----+-------------------------------+--------+

Last comment by: trey

comments (1)Comments Feeds

Share this article:

I bow to your wizardry, sir. This is some genius SQL duct tapery I've added to my arsenal .. but tweaked a bit for my purposes, so you could match an entire string, instead of just a char by doing: select `id`, `url`, (LENGTH(`url`) - LENGTH(REPLACE(`url`, 'string_to_count', '')))/length('string_to_count') as `number` from `url`;

Comment by treyWednesday, February 22, 2012 EST

Add a comment

* is required
The posting of advertisements, profanity, or personal attacks is prohibited.
Please review our terms of use

Link to this Article

To link directly to this article from your web site, use one of the following snippets below.

Count the number of occurrences of a char in MySQL | Book Of Zeus

<a href="http://www.bookofzeus.com/articles/count-the-number-of-occurrences-of-a-char-in-mysql/" title="Count the number of occurrences of a char in MySQL">Count the number of occurrences of a char in MySQL | Book Of Zeus</a>

Short URL:

Count the number of occurrences of a char in MySQL | Book Of Zeus

<a href="http://s.bookofzeus.com/XXgDS" title="Count the number of occurrences of a char in MySQL Short URL">Count the number of occurrences of a char in MySQL | Book Of Zeus</a>