MySQL- Counting The Total Occurances Of A Regular Expression In A Table Column

Jul 16th, 2009
Maruf scribbled this post.

Ok, so here’s the scenerio- you have a table like this in your DB:

The question is:

How do you count how many images there are in the images column for the entire table?

From what I’m aware, you can’t do it with a standard MySQL function. So the solution? You have to create your own function. The function needs to count how many times “.jpg” is present in the “image” column.

Solution: Step 1- Create the Function

1
2

CREATE FUNCTION substrCount(x varchar(255), delim varchar(12)) returns int
return (length(x)-length(REPLACE(x,delim, '')))/length(delim);

Run the above query to create your MySQL function.

Solution: Step 2- Run the query with your new function

1

SELECT SUM(substrCount(images,'.jpg')) from TABLE

Run that query and you’ll get the total times “.jpg” occurs in the ‘images’ column, consequently giving you a total count of how many images there are.

Notes

The MySQL function you created (substrCount) will remain stored in your DB until it is manually dropped, so you won’t need to create it again- you can call it whenever you want.