Can you split/explode a field in a MySQL query?

Question

I have to create a report on some student completions. The students each belong to one client. Here are the tables (simplified for this question).

CREATE TABLE  `clients` (
  `clientId` int(10) unsigned NOT NULL auto_increment,
  `clientName` varchar(100) NOT NULL default '',
  `courseNames` varchar(255) NOT NULL default ''
)

The courseNames field holds a comma-delimited string of course names, eg "AB01,AB02,AB03"

CREATE TABLE  `clientenrols` (
  `clientEnrolId` int(10) unsigned NOT NULL auto_increment,
  `studentId` int(10) unsigned NOT NULL default '0',
  `courseId` tinyint(3) unsigned NOT NULL default '0'
)

The courseId field here is the index of the course name in the clients.courseNames field. So, if the client's courseNames are "AB01,AB02,AB03", and the courseId of the enrolment is 2, then the student is in AB03.

Is there a way that I can do a single select on these tables that includes the course name? Keep in mind that there will be students from different clients (and hence have different course names, not all of which are sequential,eg: "NW01,NW03")

Basically, if I could split that field and return a single element from the resulting array, that would be what I'm looking for. Here's what I mean in magical pseudocode:

SELECT e.`studentId`, SPLIT(",", c.`courseNames`)[e.`courseId`]
FROM ...

Answer 1

Read the comments on this page for a plethora of solutions to the string-splitting problem: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html.

Answer 2

There's an easier way, have a link table, i.e.:

Table 1: clients, client info, blah blah blah

Table 2: courses, course info, blah blah

Table 3: clientid, courseid

Then do a JOIN and you're off to the races.

Answer 3

Here's how you do it for SQL Server. Someone else can translate it to MySQL. Parsing CSV Values Into Multiple Rows.

SELECT Author, 
NullIf(SubString(',' + Phrase + ',' , ID , CharIndex(',' , ',' + Phrase + ',' , ID) - ID) , '') AS Word 
FROM Tally, Quotes 
WHERE ID <= Len(',' + Phrase + ',') AND SubString(',' + Phrase + ',' , ID - 1, 1) = ',' 
AND CharIndex(',' , ',' + Phrase + ',' , ID) - ID > 0

The idea is to cross join to a predefined table Tally which contains integer 1 through 8000 (or whatever big enough number) and run SubString to find the right ,word, position.

Answer 4

Here's what I've got so far (found it on the page Ben Alpert mentioned):

SELECT REPLACE(
    SUBSTRING(
        SUBSTRING_INDEX(c.`courseNames`, ',', e.`courseId` + 1)
        , LENGTH(SUBSTRING_INDEX(c.`courseNames`, ',', e.`courseId`)
    ) + 1)
    , ','
    , ''
)
FROM `clients` c INNER JOIN `clientenrols` e USING (`clientId`)