Count Number Of Characters In A String With MySQL

Thursday, September 30, 2010 - 18:00

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.

philipnorton42's picture

Philip Norton

Phil is the founder and administrator of #! code and is an IT professional working in the North West of the UK.
Google+ | Twitter


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

Add new comment