3

I would like to pass the SEPARATOR value of a GROUP_CONTACT as a variable (or function parameter), however this code will fail

SET @sep = ' ';
SELECT
    `group`,
    GROUP_CONCAT( `field` ORDER BY `idx` SEPARATOR @sep ) `fields`
FROM `table`
GROUP BY `group`;

I know I can do something like

SELECT
    `group`,
    SUBSTRING(
      GROUP_CONCAT( CONCAT(`field`,@sep) ORDER BY `idx` SEPARATOR ''),
      1,
      LENGTH(
        GROUP_CONCAT( CONCAT(`field`,@sep) ORDER BY `idx` SEPARATOR '')
      )-LENGTH(@sep)
    ) `fields`
FROM `table`
GROUP BY `group`;

But it would be nicer to have a more concise syntax.

Edit:

SELECT
    `group`,
    SUBSTRING(
      GROUP_CONCAT( CONCAT(@sep,`field`) ORDER BY `idx` SEPARATOR ''),
      LENGTH(@sep)+1
    ) `fields`
FROM `table`
GROUP BY `group`;

Is a little simpler, but not satisfactory enough.

Carlos Eugenio Thompson Pinzón
  • 2,656
  • 4
  • 18
  • 22
  • "To specify a separator explicitly, use SEPARATOR followed by the string literal value that should be inserted between group values" . Only string literal are accepted, not variables. Refer: https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat – Madhur Bhaiya Nov 19 '18 at 04:01
  • Why would `LENGTH(@sep)+1` be a second parameter to `group_concat()`? What relevance would an integer have in that location? Stackoverflow is not the place for a feature wishlist. – Paul Maxwell Nov 19 '18 at 04:26
  • I'm voting to close this question as off-topic because it appears to be a feature wish – Paul Maxwell Nov 19 '18 at 04:28
  • 1
    @Used_By_Already `LENGTH(@sep)+1` is the second parameter to `SUBSTRING` – Nick Nov 19 '18 at 05:22
  • still makes no sense to me, you are asking for something that does not exist, and you appear to know it does not exist – Paul Maxwell Nov 19 '18 at 05:25
  • 1
    @Used_By_Already, asking for both workarounds explanation of the rationale behind why only literals are allowed. I think the general public could benefit from this question and the answers. – Carlos Eugenio Thompson Pinzón Nov 19 '18 at 11:30
  • While there might be some folk interested in an opinion on why parameters can't be used, I'm afraid this is not an opinion site, and "calling for opinion" is a cause for closing questions too. – Paul Maxwell Nov 19 '18 at 11:46

2 Answers2

1

You could use a prepared statement:

SET @sep = '**';
SET @sql = CONCAT('SELECT `group`, GROUP_CONCAT( `field` ORDER BY `idx` SEPARATOR "',
@sep, '") `fields` FROM `table` GROUP BY `group`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I created a small demo on dbfiddle:

create table `table` (idx int auto_increment primary key, field varchar(10), `group` int);
insert into `table` (field, `group`) values
('hello', 4),
('world', 4),('today', 4),('hello', 3),('world', 3),
('hello', 5),('today', 5),('world', 5),('goodbye', 5)

Output of the prepared statement is:

group   fields
3       hello**world
4       hello**world**today
5       hello**today**world**goodbye
Nick
  • 138,499
  • 22
  • 57
  • 95
0

A very simple and straightforward solution is to use REPLACE after you have used GROUP_CONCAT. So, initially, the SEPARATOR could be just a comma, then you can replace the comma with the value of your parameter.

antoines
  • 76
  • 1
  • 4