Is there a way to write recursive query in mysql. Equivalent of connect by (level or Prior) in oracle. I searched google as well as stackoverflow and there is no direct eqivalent. But is there any work around to get it.
I have a string, i have to iterate through individual characters in the string and print only the distinct characters of the string.
recursive
recusiv
I came up with this "simple" solution.
Functions used:
GROUP_CONCAT: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
SUBSTRING http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring
Live demo: http://www.sqlfiddle.com/#!2/d41d8/19186
SELECT
GROUP_CONCAT( chars.c SEPARATOR '') AS allchars
FROM (
SELECT DISTINCT
SUBSTRING( str.str, pos.pos, 1 ) AS c
FROM
( SELECT x1.x + x2.x*10 AS pos
FROM
( SELECT 0 AS x 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 x1
INNER JOIN
( SELECT 0 AS x 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 x2
) AS pos
INNER JOIN
( SELECT 'recursive' AS str UNION ALL
SELECT 'XYZ'
) AS str
) AS chars
recursive and XYZ). SELECT DISTINCT SUBSTRING makes a table of one string's character which table is re-joined to a string by the outer GROUP_CONCAT. Seriously, it doesn't seem to be a good idea to do it in MySQL, unless they have some new functions since.
