-2

I came across an old post and tried the code with a project that I am working on, and it worked, but I am still confused as to why, could anyone here please unpack the logic behind the code here? I am specifically referring to this fiddle.

I understand substring_index, but not sure what "numbers" does, as well as the char length calculations.

Thanks in advance.

Sisi Yao
  • 1
  • numbers is just a table with sequential numbers – Hogan Jun 15 '22 at 18:59
  • honestly I would use the json parse examples myself this seems very wonky. I would also probably use row_number partitioned by id instead of a numbers table here. – Hogan Jun 15 '22 at 19:47
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Jun 16 '22 at 07:45

1 Answers1

1

The numbers table is a way to create an ad hoc table that consists of sequential integers.

mysql> SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4;
+---+
| n |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+

These numbers are used to extract the N'th word from the comma-separated string. It's just a guess that 4 is enough to account for the number of words in the string.

The CHAR_LENGTH() expression is a tricky way to count the words in the command-separated string. The number of commas determines the number of words. So if you compare the length of the string to the length of that string with commas removed, it tells you the number of commas, and therefore the number of words.

mysql> set @string = 'a,b,c,d,e,f';

mysql> select char_length(@string) - char_length(replace(@string, ',', '')) + 1 as word_count;
+------------+
| word_count |
+------------+
|          6 |
+------------+

Confusing code like this is one of the many reasons it's a bad idea to store data in comma-separated strings.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828