Select split substring as in MySQL

[proggR]

In a statistics table I'm working with there is a row called module. Its been slightly repurposed for the use of communities. Usually it would just contain one piece of information but in the communities it contains 3: the first specifies that it is a community, the second is the community id, the third is the module within the community that was accessed. These are all delimited by colons. Ex:

communities:2:galleries
communities:2:discussions

 

The goal is to grab only the community ids that haven't been accessed in the past month. The problem is that I can't think of anyway to get just the id from that string without splitting it with PHP which won't work for what I need. The pseudocode for what I need to do is below.

SELECT <community_id> FROM statistics WHERE timestamp < 6 months ago AND <community_id> NOT IN (SELECT <community_id> FROM statistics WHERE timestamp > 6 months ago)

 

Is there anyway to split on a character in MySQL? I know how many characters will be before the community id, "communities:", but there's no way to know how long the id will be so SUBSTRING wouldn't really work.

 

Thanks in advance for any help.

 

[AMcHarg]

Why don't you just create another two fields and separate the data? 

[proggR]

I wouldn't likely do it this way given the option. It's part of a larger project and rather than having a separate table for statistics related just to communities the goal is to keep just the one table that works across the board. It was suggested to use the colon as a delimiter which has been working well for selecting up until now. The problem is that I need to select JUST the community ids from within the string. It seems possible with some  messy combination of SUBSTRING_INDEX calls to trim of everything to the left and right of the community id but that would need to be nested in some way that I'd rather not have to figure out if I don't have to.

[fenway]

Well, you're not supposed to store delimited data when you (a) aren't storing individual elements of an array and (b) need access to an arbitrary element.

 

You're missing some tables.

 

And SUBSTRING_INDEX() can help you if the want the "inside".