CODEWITHSUNDEEP
mysqlsqlmariadb
monotonehell
monotonehell

Reputation: 3

choice of separator ignored by CONCAT_WS (mysql/mariadb)

I may be missing something simple, but it seems that the separator I'm choosing for CONCAT_WS doesn't seem to be working when inside a more complex query.

First I tested CONCAT_WS by itself:

SELECT CONCAT_WS(' / ', 'a', 'b', 'c') AS `test`;

Which returned:

test
a / b / c

Then I tried it inside a GROUP_CONCAT which I intended to use:

SELECT GROUP_CONCAT( CONCAT_WS(' / ', 'a', 'b', 'c') ) AS `test`;

Which returned:

test
a / b / c

Okay all good, so I tried it in my proper query on live data:

SELECT `Box Barcode`,`Title`,`Start Date`,`End Date`,`Offsite Indicator`,`MIA`,
GROUP_CONCAT( CONCAT_WS(' / ',`Description`) ) AS `Contents` 
FROM `boxes` 
GROUP BY `Box Barcode` ORDER BY `Box Barcode` ASC ;

Which returned the correct results, except with commas instead of / For example here's one line of the results:

Box Barcode  Title     Start Date  End Date    Offsite Indicator  MIA    Contents
C5500501127  PPS AMEX  30/06/2002  30/06/2002  TRUE               FALSE  PPS AMEX,PPS AMEX,PPS DINERS,PPS DINERS,PPS LIABILITIES,PPS PROPERTY PLAN AND EQUIPMENT,PPS ASSETS

A couple hour's of googling have not helped. Anyone have any idea where I'm going wrong? This is via a PhpMyAdmin web interface on a Debian server running MariaDB (libmysql - 5.5.53).

Upvotes: 0

Views: 198

Answers (1)

Arion
Arion

Reputation: 31239

It might be that the GROUP_CONCAT also has a separator.

SELECT GROUP_CONCAT( CONCAT_WS(' / ',`Description`) ) AS `Contents`

will result in a , separation

this here:

GROUP_CONCAT( CONCAT_WS(' / ',`Description`) SEPARATOR ' / ')

Will make the GROUP_CONCAT have / separator

Upvotes: 1

Related Questions