CODEWITHSUNDEEP
mysqlgroup-concatseparator
Sarita Tewari
Sarita Tewari

Reputation: 37

IN mysql function group_concat change separator dynamically

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.

Upvotes: 3

Views: 1188

Answers (1)

userlond
userlond

Reputation: 3824

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>

Upvotes: 3

Related Questions