Select distinct substrings that are in a comma separated string | MySql

Question

I've a csv file that I imported to my database in which there are a column with comma separated values and what I wanna do is get all the values without duplicates from these column and put it in a only one column

This it's the table

And this it's what I wanna do:

I don't want to modify the original column just show these values in this shape for know all diferentes values that the comma separated strings could take

I think that in this question SQL split values to multiple rows there are the answer. It provides this code:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  numbers inner join tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

But I can't deeply understand it. Why there are two substring_index? What's it's the use of n.numbers? What's the use of "name" keyword? etc.

It would greatly appreciated if someone can clarify me that or if in other question it better explained then in this case this question will can linked to that or borrow

Answer 1

Why there are two substring_index?

Imagine that tablename.name = 'A,B,C' and numbers.n = 2.

Look the result of inner function call which is

SUBSTRING_INDEX(tablename.name, ',', numbers.n)
-- i.e.
SUBSTRING_INDEX('A,B,C', ',', 2)

It gets the subpart until specified, 2nd, delimiter - i.e. it gives 'A,B'.

Now look what the outer function do with this output. It is

SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1)
-- i.e.
SUBSTRING_INDEX('A,B', ',', -1)

It gets the subpart until specified, 1st, delimiter, from the end - i.e. it gets last substring which is 'B'.

Finally the construction takes 2nd substring like specified by numbers.n = 2.