Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

mysql

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 ... 
like image 378
nickf Avatar asked Jan 23 '09 04:01

nickf


People also ask

Is there a split function in MySQL?

There is no string split function in MySQL. so you have to create your own function.

How do I separate text in MySQL?

To split a string in MySQL, you need to make use of the SUBSTRING_INDEX function that is provided by MySQL. The SUBSTRING_INDEX() function allows you to extract a part of a complete string. The syntax of the function is as follows: SUBSTRING_INDEX(expression, delimiter, count);

How do you explode a query?

If EXPLODE is applied on an instance of SQL. ARRAY <T>, the resulting rowset contains a single column of type T where each item in the array is placed into its own row. If the array value was empty or null, then the resulting rowset is empty. If EXPLODE is applied on an instance of SQL.


1 Answers

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.

like image 159
Melchior Blausand Avatar answered Sep 18 '22 12:09

Melchior Blausand