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 |
+----+-------------------------------+--------+
Be the first to reply!
Share this article: