【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;

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

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