I want to use separator for group_concat() function in mysql.But I want to change the separator dynamically.I mean the separator value is coming from a table and that is different for every row.
I couldn't found any solution for that please help me.
Asked
Active
Viewed 1,061 times
3
-
1Probably an indication that you have a huge problem with your data. For this you need dynamic sql – e4c5 Jan 10 '17 at 07:15
-
IN other words I am passing the separator value in parameter then how to use that please help.. – Sarita Tewari Jan 10 '17 at 07:25
-
I got the solution:we can use any separator inside ' ' but at last replace with that one which we are passing. – Sarita Tewari Jan 10 '17 at 07:41
-
@SaritaTewari, faced the same problem. Could you clarify your solution, please? – userlond Jun 13 '17 at 08:58
1 Answers
3
Finally got a solution.
Let's say we have some MySQL routine. We need to GROUP_CONCAT some value with special SEPARATOR (default is ','). But SEPARATOR isn't static, it's got from another table, for ex. from some "settings" table.
DECLARE url_delimiter VARCHAR(255);
SELECT catalog_url_delimiter
INTO url_delimiter
FROM settings;
We can't use variable as SEPARATOR parameter directly:
-- doesn't work
SELECT GROUP_CONCAT(`some_table`.id SEPARATOR url_delimiter)
FROM <some query>
But we can use some dummy separator and replace it with valid as below:
SELECT
REPLACE(
GROUP_CONCAT(`some_table`.id SEPARATOR 'some-tricky-dummy-separator'),
'some-tricky-dummy-separator',
url_delimiter
)
FROM <some query>
