Splitting a String in MySQL

Sometimes you’ll end up working with a MySQL field that contains multiple pieces of information. This can be a delimited list, or, in my case, nested categories. I’ve been using a single field to contain information on the genre of a book which is often a subcategory of a larger genre. For example, an Historical Romantic Fiction book would have the genre “Fiction/Romance/Historical” in my genre field. When giving our employees a list of genres to select when adding product information to a book, I don’t want to just give them all 3,000 sub-genres, instead I want to give them level-by-level genres to choose from. i.e. “Select from Nonfiction or Fiction” and if they select Fiction: “Select from Romance, Fantasy, or Classic” and if they select Romance: “Select from Historical or Contemporary” etc.

In doing so I need to pull out, level by level, the existing genres. To do this for the first level I use the following query:

SELECT SUBSTRING_INDEX(genre,”/”,1), COUNT(eAN) FROM `inventory_product_info` GROUP BY SUBSTRING_INDEX(genre,”/”,1) ORDER BY COUNT(eAN) DESC

I’m using the SUBSTRING_INDEX() MySQL function to get a substring from the beginning of a string (in this case the contents of the genre field) until the 1st occurrence of the “/” character. I’m both returning the results of the SUBSTRING_INDEX() and grouping by it to get a count of all products which use that top-level genre.

Leave a Reply