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

There's an easier way, have a link table, i.e.:

Table 1: clients, client info, blah blah blah

Table 2: courses, course info, blah blah

Table 3: clientid, courseid

Then do a JOIN and you're off to the races.

SELECT
  tab1.std_name, tab1.stdCode, tab1.payment,
  SUBSTRING_INDEX(tab1.payment, '|', 1) as rupees,
  SUBSTRING(tab1.payment, LENGTH(SUBSTRING_INDEX(tab1.payment, '|', 1)) + 2,LENGTH(SUBSTRING_INDEX(tab1.payment, '|', 2))) as date
FROM (
  SELECT DISTINCT
    si.std_name, hfc.stdCode,
    if(isnull(hfc.payDate), concat(hfc.coutionMoneyIn,'|', year(hfc.startDtae), '-',  monthname(hfc.startDtae)), concat(hfc.payMoney, '|', monthname(hfc.payDate), '-', year(hfc.payDate))) AS payment
  FROM hostelfeescollection hfc
  INNER JOIN hostelfeecollectmode hfm ON hfc.tranId = hfm.tranId
  INNER JOIN student_info_1 si ON si.std_code = hfc.stdCode
  WHERE hfc.tranId = 'TRAN-AZZZY69454'
) AS tab1

Search in a column containing comma-separated values

MySQL has a dedicated function FIND_IN_SET() that returns field index if the value is found in a string containing comma-separated values.

For example, the following statement returns one-based index of value C in string A,B,C,D.

SELECT FIND_IN_SET('C', 'A,B,C,D') AS result;
+--------+
| result |
+--------+
|      3 |
+--------+

If the given value is not found, FIND_IN_SET() function returns 0.

SELECT FIND_IN_SET('Z', 'A,B,C,D') AS result;
+--------+
| result |
+--------+
|      0 |
+--------+

You can do this with JSON in more recent MySQL versions. It's a blast. We will have a quick preparation to create a numbers table. Then first we create an intermediary table to convert the comma delimited strings into a json array then we will use json_extract to blast them apart. I am encapsulating the strings in quotes carefully escaping existing quotes because I had semicolon separated strings containing commas.

So to create the numbers table, hopefully you have more clients than courses, choose an adequately big table if not.

CREATE TABLE numbers (n int PRIMARY KEY);
INSERT INTO numbers 
SELECT @row := @row + 1
FROM clients JOIN (select @row:=0) t2;

Add LIMIT 50 if you know you only have 50 courses. Now, that was easy, wasn't it? Now on to the real work, honestly it's the quotes that make it uglier but at least it's more generic that way:

CREATE TABLE json_coursenames 
SELECT clientId,clientName,CONCAT('["', REPLACE(REPLACE(courseName,'"','\\"'), ',', '","'), '"]') AS a
FROM clients;

CREATE TABLE extracted
SELECT clientId,clientName,REPLACE(TRIM(TRIM('"' FROM JSON_EXTRACT(a, concat('$[', n, ']')))), '\\"', '"')
FROM json_coursenames
INNER JOIN numbers ON n < JSON_LENGTH(a);

Wheee!

The meat here are these two: the CONCAT('["', REPLACE(coursename, ',', '","'), '"]') (I dropped the second REPLACE to make it more visible) will convert foo,bar,bar into "foo","bar","baz". The other trick is JSON_EXTRACT(a, concat('$[', n, ']') will become JSON_EXTRACT(a, $[12]) and that's the 13th element in the array, see JSON Path syntax.

SQL：

SELECT
    DISTINCT *
FROM
    JSON_TABLE(
        concat('["',replace('AB01,AB02,AB03',',','","'),'"]'),
        '$[*]'
        COLUMNS(
            Value VARCHAR(50) PATH '$'
            )
        ) data
WHERE Value != '';

RESULT： | | Value | | -------- | :-------------- | | 1 | AB01 | | 2 | AB02 | | 3 | AB03 |

Here's how you do it for SQL Server. Someone else can translate it to MySQL. Parsing CSV Values Into Multiple Rows.

SELECT Author, 
NullIf(SubString(',' + Phrase + ',' , ID , CharIndex(',' , ',' + Phrase + ',' , ID) - ID) , '') AS Word 
FROM Tally, Quotes 
WHERE ID <= Len(',' + Phrase + ',') AND SubString(',' + Phrase + ',' , ID - 1, 1) = ',' 
AND CharIndex(',' , ',' + Phrase + ',' , ID) - ID > 0

The idea is to cross join to a predefined table Tally which contains integer 1 through 8000 (or whatever big enough number) and run SubString to find the right ,word, position.

Here's what I've got so far (found it on the page Ben Alpert mentioned):

SELECT REPLACE(
    SUBSTRING(
        SUBSTRING_INDEX(c.`courseNames`, ',', e.`courseId` + 1)
        , LENGTH(SUBSTRING_INDEX(c.`courseNames`, ',', e.`courseId`)
    ) + 1)
    , ','
    , ''
)
FROM `clients` c INNER JOIN `clientenrols` e USING (`clientId`)