Inverse Grouping in SPL

Question
I need a list of user IDs (course_user_ids) that is currently stored in a single field of a larger table.

I have a table called courses that contains course information with course_id and course_students as such:

-----------------------------------------------------------

| course_id | course_students |

----------------------------------------------------------

| 1 | a:3:{i:0;i:12345;i:1;i:22345;i:2;i:323456;} |

-----------------------------------------------------------

| 2 | a:32:{…} |

-----------------------------------------------------------

The course_students part contains 3 chunks of information:

1.       The number of students (a:3:{…) -- not needed

2.         The order/key for the array of each student ({i:0;… i:1;… i:2; …}) -- also not needed

3.         The course_user_id(i:12345; … i:22345;… i:32345;)

I only need the course_user_id and the original course_id, resulting in a new table that I can use for joins/subqueries like this:

------------------------------

| course_id | course_user_id |

------------------------------

| 1 | 12345 |

------------------------------

| 1 | 22345 |

------------------------------

| 1 | 323456 |

------------------------------

(Ideally able to continue to break out values for other course_ids and course_user_ids, but not a priority:)

| … | … |

------------------------------

| 2 | … |

------------------------------

| 2 | … |

------------------------------

| 97 | … |

------------------------------

| 97 | … |

------------------------------

| … | … |

------------------------------

Note: the course_user_id can vary in length (some are 5 digits, some are 6)

Any ideas would be much appreciated!

Below is a solution, but I don’t think it’s a good one

select c.course_id,u.user_id

from

courses c

join users u

on u.user_id=if(instr(c.course_students,concat(“:”,u.user_id,“;”))>0,u.user_id,c.course_students)

 

Answer
First, parse strings surrounded by the braces; then get the desired strings from positions of even numbers; last, generate records according to the sequence of strings. This is inverse grouping and it’s hard to do it in SQL. You can easily implement it in SPL. Here’s SPL code:

 

A

1

$select   course_id,course_students from tb

2

=A1.run(course_students=course_students.split@b(";").step(2,2).(~.split(":")(2)))

3

=A2.news(course_students;A2.course_id,~:course_user_id)

A2: Get a sequence of substrings by splitting each course_students value; the SPL split() function splits a string to generate a sequence; step(2,2) function gets members from positions that are even numbers;

undefined

A3: Generate a new table sequence according to A2’s sequence.

undefined

You can call the SPL script in another application via esProc JDBC. For details, see How to Call an SPL Script in Java