How could I pass the separator as a variable in MySQL GROUP_CONCAT?
2
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.
mysql group-concat
asked Nov 19 '18 at 3:57
Carlos Eugenio Thompson PinzónCarlos Eugenio Thompson Pinzón
1,07141119
|
show 2 more comments
2
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.
mysql group-concat
asked Nov 19 '18 at 3:57
Carlos Eugenio Thompson PinzónCarlos Eugenio Thompson Pinzón
1,07141119
"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: dev.mysql.com/doc/refman/8.0/en/…
– Madhur Bhaiya
Nov 19 '18 at 4:01
Why wouldLENGTH(@sep)+1be a second parameter togroup_concat()? What relevance would an integer have in that location? Stackoverflow is not the place for a feature wishlist.
– Used_By_Already
Nov 19 '18 at 4:26
I'm voting to close this question as off-topic because it appears to be a feature wish
– Used_By_Already
Nov 19 '18 at 4:28
@Used_By_AlreadyLENGTH(@sep)+1is the second parameter toSUBSTRING
– Nick
Nov 19 '18 at 5: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
– Used_By_Already
Nov 19 '18 at 5:25
|
show 2 more comments
2
2
2
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.
mysql group-concat
asked Nov 19 '18 at 3:57
Carlos Eugenio Thompson PinzónCarlos Eugenio Thompson Pinzón
1,07141119
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.
mysql group-concat
mysql group-concat
asked Nov 19 '18 at 3:57
Carlos Eugenio Thompson PinzónCarlos Eugenio Thompson Pinzón
1,07141119
asked Nov 19 '18 at 3:57
Carlos Eugenio Thompson PinzónCarlos Eugenio Thompson Pinzón
1,07141119
edited Nov 19 '18 at 4:02
Carlos Eugenio Thompson Pinzón
asked Nov 19 '18 at 3:57
Carlos Eugenio Thompson PinzónCarlos Eugenio Thompson Pinzón
1,07141119
asked Nov 19 '18 at 3:57
Carlos Eugenio Thompson PinzónCarlos Eugenio Thompson Pinzón
1,07141119
asked Nov 19 '18 at 3:57
Carlos Eugenio Thompson PinzónCarlos Eugenio Thompson Pinzón
1,07141119
1,07141119
"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: dev.mysql.com/doc/refman/8.0/en/…
– Madhur Bhaiya
Nov 19 '18 at 4:01
Why wouldLENGTH(@sep)+1be a second parameter togroup_concat()? What relevance would an integer have in that location? Stackoverflow is not the place for a feature wishlist.
– Used_By_Already
Nov 19 '18 at 4:26
I'm voting to close this question as off-topic because it appears to be a feature wish
– Used_By_Already
Nov 19 '18 at 4:28
@Used_By_AlreadyLENGTH(@sep)+1is the second parameter toSUBSTRING
– Nick
Nov 19 '18 at 5: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
– Used_By_Already
Nov 19 '18 at 5:25
|
show 2 more comments
"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: dev.mysql.com/doc/refman/8.0/en/…
– Madhur Bhaiya
Nov 19 '18 at 4:01
Why wouldLENGTH(@sep)+1be a second parameter togroup_concat()? What relevance would an integer have in that location? Stackoverflow is not the place for a feature wishlist.
– Used_By_Already
Nov 19 '18 at 4:26
I'm voting to close this question as off-topic because it appears to be a feature wish
– Used_By_Already
Nov 19 '18 at 4:28
@Used_By_AlreadyLENGTH(@sep)+1is the second parameter toSUBSTRING
– Nick
Nov 19 '18 at 5: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
– Used_By_Already
Nov 19 '18 at 5:25
"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: dev.mysql.com/doc/refman/8.0/en/…
– Madhur Bhaiya
Nov 19 '18 at 4:01
"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: dev.mysql.com/doc/refman/8.0/en/…
– Madhur Bhaiya
Nov 19 '18 at 4: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.– Used_By_Already
Nov 19 '18 at 4:26
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.– Used_By_Already
Nov 19 '18 at 4:26
I'm voting to close this question as off-topic because it appears to be a feature wish
– Used_By_Already
Nov 19 '18 at 4:28
I'm voting to close this question as off-topic because it appears to be a feature wish
– Used_By_Already
Nov 19 '18 at 4:28
@Used_By_Already
LENGTH(@sep)+1 is the second parameter to SUBSTRING– Nick
Nov 19 '18 at 5:22
@Used_By_Already
LENGTH(@sep)+1 is the second parameter to SUBSTRING– Nick
Nov 19 '18 at 5: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
– Used_By_Already
Nov 19 '18 at 5:25
still makes no sense to me, you are asking for something that does not exist, and you appear to know it does not exist
– Used_By_Already
Nov 19 '18 at 5:25
|
show 2 more comments
1 Answer
1
active
oldest
votes
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
answered Nov 19 '18 at 4:18
NickNick
27.1k111940
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53368125%2fhow-could-i-pass-the-separator-as-a-variable-in-mysql-group-concat%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 19 '18 at 4:18
NickNick
27.1k111940
add a comment |
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
answered Nov 19 '18 at 4:18
NickNick
27.1k111940
add a comment |
1
1
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
answered Nov 19 '18 at 4:18
NickNick
27.1k111940
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
answered Nov 19 '18 at 4:18
NickNick
27.1k111940
answered Nov 19 '18 at 4:18
NickNick
27.1k111940
answered Nov 19 '18 at 4:18
NickNick
27.1k111940
answered Nov 19 '18 at 4:18
NickNick
27.1k111940
27.1k111940
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53368125%2fhow-could-i-pass-the-separator-as-a-variable-in-mysql-group-concat%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
"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: dev.mysql.com/doc/refman/8.0/en/…
– Madhur Bhaiya
Nov 19 '18 at 4:01
Why would
LENGTH(@sep)+1be a second parameter togroup_concat()? What relevance would an integer have in that location? Stackoverflow is not the place for a feature wishlist.– Used_By_Already
Nov 19 '18 at 4:26
I'm voting to close this question as off-topic because it appears to be a feature wish
– Used_By_Already
Nov 19 '18 at 4:28
@Used_By_Already
LENGTH(@sep)+1is the second parameter toSUBSTRING– Nick
Nov 19 '18 at 5: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
– Used_By_Already
Nov 19 '18 at 5:25