Count Number Of Characters In A String With MySQL

Today I needed to grab some data from a table where there was more than one occurrence of a string within another string. Basically, I needed to find all URL's from a table that were more than 3 levels deep (i.e. with 3 slashes), but realised there wasn't a function to do this in MySQL. I found an alternative method, but it got me thinking on how that might be possible.

Finding the solution wasn't too difficult really, all I needed to do was find out the length of the URL string and then subtract the length of the string with the slashes removed. This MySQL statement will give you the URL and the number of slashes in that URL in a table.

SELECT url, (LENGTH(url)-LENGTH(REPLACE(url, '/', ''))) AS depth
FROM table;

This works fine for single character string like the slash, but if you want to do the same thing with multi character strings then you need to divide the difference by the length of the string. Here is an example.

SELECT body, ROUND((LENGTH(body)-LENGTH(REPLACE(body, 'text', '')))/LENGTH('text')) AS depth
FROM table;

This will give you the number of occurrences of the word 'text' in each body field in a table.


SELECT body, ROUND((LENGTH(body)-LENGTH(REPLACE(body, 'text', '')))/LENGTH('text')) AS depth FROM table;

very useful.



Add new comment

The content of this field is kept private and will not be shown publicly.
2 + 1 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.