UNNEST function in MYSQL like POSTGRESQL
Yes, it is possible. From technical viewpoint, you can achieve that with one query. But the thing is - most probably, you are trying to pass some logic from application to data storage. Data storage is intended to store data, not to represent/format it or, even more, apply some logic to it.
Yes, MySQL doesn't have arrays data type, but in most cases it won't be a problem and architecture can be created so it will fit those limitations. And in any case, even if you'll achieve it somehow (like - see below) - you won't be possible to properly work later with that data, since it will be just result set. You may store it, of course - so to, let's say, index later, but then it's again a task for an application - so to create that import.
Also, make sure that it is not a Jaywalker case, so not about storing delimiter-separated values and later trying to extract them.
From technical viewpoint, you can do it with Cartesian product of the two row sets. Then use a well known formula:
N = d1x101 + d2x102 + ...
Thus, you'll be able to create a "all-numbers" table and later iterate through it. That iteration, together with MySQL string functions, may lead you to something like this:
SELECT data FROM ( SELECT @next:=LOCATE(@separator,@search, @current+1) AS next, SUBSTR(SUBSTR(@search, @current, @next-@current), @length+1) AS data, @next:=IF(@next, @next, NULL) AS marker, @current:=@next AS current FROM (SELECT 0 as i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as n1 CROSS JOIN (SELECT 0 as i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as n2 CROSS JOIN (SELECT -- set your separator here: @separator := ',', -- set your string here: @data := '1,25,42,71', -- and do not touch here: @current := 1, @search := CONCAT(@separator, @data, @separator), @length := CHAR_LENGTH(@separator)) AS init ) AS joins WHERE marker IS NOT NULL
The corresponding fiddle would be here.
You should also notice: this is not a function. And with functions (I mean, user-defined with
CREATE FUNCTION statement) it's impossible to get result row set since function in MySQL can not return result set by definition. However, it's not true to say that it's completely impossible to perform requested transformation with MySQL.
But remember: if you are able to do something, that doesn't mean you should do it.
This sample fetchs all "catchwords" from Table data, wich are seperated by ","Maximum values in the commaseparated list is 100
WITH RECURSIVE num (n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM num WHERE n<100 -- change this, if more than 100 elements ) SELECT DISTINCT substring_index(substring_index(catchwords, ',', n), ',', -1) as value FROM data JOIN num ON char_length(catchwords) - char_length(replace(catchwords, ',', '')) >= n - 1