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

Until now, I wanted to keep those comma separated lists in my SQL db - well aware of all warnings!

I kept thinking that they have benefits over lookup tables (which provide a way to a normalized data base). After some days of refusing, I've seen the light:

• Using lookup tables is NOT causing more code than those ugly string operations when using comma separated values in one field.
• The lookup table allows for native number formats and is thus NOT bigger than those csv fields. It is SMALLER though.
• The involved string operations are slim in high level language code (SQL and PHP), but expensive compared to using arrays of integers.
• Databases are not meant to be human readable, and it is mostly stupid to try to stick to structures due to their readability / direct editability, as I did.

In short, there is a reason why there is no native SPLIT() function in MySQL.