title: Can you split/explode a field in a MySQL query?
body: <p>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).</p>
  
  <pre><code>CREATE TABLE  `clients` (
    `clientId` int(10) unsigned NOT NULL auto_increment,
    `clientName` varchar(100) NOT NULL default '',
    `courseNames` varchar(255) NOT NULL default ''
  )
  </code></pre>
  
  <p>The <code>courseNames</code> field holds a comma-delimited string of course names, eg "AB01,AB02,AB03"</p>
  
  <pre><code>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'
  )
  </code></pre>
  
  <p>The <code>courseId</code> field here is <strong>the index of the course name in the clients.courseNames field</strong>. So, if the client's <code>courseNames</code> are "AB01,AB02,AB03", and the <code>courseId</code> of the enrolment is <code>2</code>, then the student is in AB03.</p>
  
  <p>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")</p>
  
  <p>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:</p>
  
  <pre><code>SELECT e.`studentId`, SPLIT(",", c.`courseNames`)[e.`courseId`]
  FROM ...
  </code></pre>