Valentin Grégoire Valentin Grégoire - 5 months ago 15
SQL Question

Concat different values with delimiter

I have a few fields in my table, and I want to combine some of them into a string, using

-
as a separator:

select
concat(
case when a <> 'a' then concat('A = ', a)
case when b <> 'b' then concat(' - B = ', b)
...
case when z <> 'z' then concat(' - Z = ', z)
)
;


Now, if A is not included, then my result will start with
-
, and I don't want that. In reality I have like 15 cases so I can't just check if A is 1 again to insert the
-
. What is the easiest solution so that my string never starts with the delimiter?

I know I can just check if it starts with ' - ', but that looks as a dirty solution to me...

Answer

If I understand well your issue, you should try something with concat_ws like the following code (normally concat_ws will skip null values from your CASE statements):

SELECT
    CONCAT_WS(
        ' - ', 
        (CASE a WHEN 1 THEN CONCAT('A = ', a) ELSE null END), 
        (CASE b WHEN 1 THEN CONCAT('B = ', b) ELSE null END),
        ...
    )

Example below

create table table5
(   id int auto_increment primary key,
    a int not null,
    b int not null
);
insert table5(a,b) values (0,0),(1,0),(1,1),(0,1),(1,1);

SELECT 
    id, 
    CONCAT_WS( 
        ' - ',  
        (CASE a WHEN 1 THEN CONCAT('A = ', a) ELSE null END),  
        (CASE b WHEN 1 THEN CONCAT('B = ', b) ELSE null END) 
    ) as xxx 
    from table5; 
+----+---------------+
| id | xxx           |
+----+---------------+
|  1 |               |
|  2 | A = 1         |
|  3 | A = 1 - B = 1 |
|  4 | B = 1         |
|  5 | A = 1 - B = 1 |
+----+---------------+