0

This question is the continue of Select distinct substrings that are in a comma separated string | MySql. Based on the answer of these post I built this function:

create function function1(var1 int, str text)
returns text deterministic
return substring_index(substring_index(str, ",", var1), ",", -1);

that returns the substring that are before of the comma indicated by var1, so my idea was put this function in a while or other kind of loop and with a incremental variable handle the var1 and each return store it in a temporary table but I don't know how to do that exactly

Can someone give me a advice?

Caeta
  • 431
  • 3
  • 14

1 Answers1

1

This kind of thing is generally wanted where you want separate rows for each comma-separated value. For that, you need a join. Though you could use a recursive CTE to effectively loop.

Note that the double substring_index does not return the correct thing if a too-high var1 is passed; if you are bothering to create a function, you should have it check that var1 is in the range 1..number of commas in str, and perhaps return NULL otherwise? You count how many times str1 appears (non-overlapping) in str2 with:

(CHAR_LENGTH(str2)-CHAR_LENGTH(REPLACE(str2,str1,''))/CHAR_LENGTH(str1))
ysth
  • 96,171
  • 6
  • 121
  • 214