CODEWITHSUNDEEP
mysql
Caeta
Caeta

Reputation: 461

Select distinct substrings that are in a comma separated string (Part 2) | MySql

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?

Upvotes: 0

Views: 128

Answers (1)

ysth
ysth

Reputation: 98398

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))

Upvotes: 1

Related Questions