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.