Merge two colums, multiple rows ignoring duplicates - MySQL

Question

This is, maybe, a stupid question. Not for me: honestly, I'm not so skilled with MySql queries so I'm looking for a little help.

I have a table:

id |      type_a              |    type_b        |
__________________________________________________
 1 | *color_1*color_2*color_3*| *color_1*        |
 2 | *color_3*                | *color_3*color_2*|
 3 | *color_2*color_3*        | *color_4*        |
 4 | *color_1*color_3*color_4*|                  |
 5 | *color_4*                | *color_5*        |
__________________________________________________

I would like to move "type_b" column content in "type_a" column ignoring duplicate fields (delimited by * and *, ex: color_1. This kind of storage is builded by a Joomla component).

I would like to have this final result:

    id |      type_a              |  type_b |  
    _________________________________________
     1 | *color_1*color_2*color_3*|         |
     2 | *color_3*color_2*        |         |
     3 | *color_2*color_3*color_4*|         |
     4 | *color_1*color_3*color_4*|         |
     5 | *color_4*color_5*        |         |
    _________________________________________

What's the best way to accomplish something similar?

Thanks to all!

This is really hard to do in MySQL, it doesn't have any functions for splitting a string up by delimiters. You really should normalize your schema. — Barmar, Jul 30 '14 at 07:50
You could write a stored procedure to get this done, but you would better move to a better Joomla component. Storing character separated lists in a column is most times asking for trouble. — VMai, Jul 30 '14 at 07:59

score 1 · Accepted Answer · edited May 23 '17 at 11:57

You could do it with this statement (no, it's not looking nice), assuming the name of your table is example:

UPDATE
    example e1
SET
    e1.type_a = (
        SELECT
            CONCAT('*', GROUP_CONCAT(DISTINCT n1.value ORDER BY n1.value SEPARATOR '*'), '*') as type_a
        FROM ( 
            SELECT
                id, 
            CASE 
                WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_a), '*', n.n), '*', -1) = '' THEN NULL
                ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_a), '*', n.n), '*', -1)
            END value
            FROM example e CROSS JOIN (
                SELECT 
                    a.N + b.N * 10 + 1 AS n
                FROM
                    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
                   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
                ORDER BY n
            ) n
            WHERE 
                n.n <= 1 + (LENGTH(e.type_a) - LENGTH(REPLACE(e.type_a, '*', '')))
            UNION
            SELECT
                id, 
            CASE 
                WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_b), '*', n.n), '*', -1) = '' THEN NULL
                ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_b), '*', n.n), '*', -1)
            END value
            FROM example e CROSS JOIN (
                SELECT 
                    a.N + b.N * 10 + 1 AS n
                FROM
                    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
                   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
                ORDER BY n
            ) n
            WHERE 
                n.n <= 1 + (LENGTH(e.type_b) - LENGTH(REPLACE(e.type_b, '*', '')))
        ) n1
        WHERE 
            n1.id = e1.id
        GROUP BY 
            id
    ),
    e1.type_b = ''
;

Demo of the SELECT statement

Explanation

Basically I adapted the method of peterm to get the split done. I had to remove the outer * first by TRIM.

To allow the empty string as column value, I've added the CASE construct, to eliminate such values. If your column has NULL values instead, you could substitute the CASE by

SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_a), '*', n.n), '*', -1)

and

SUBSTRING_INDEX(SUBSTRING_INDEX(TRIM(BOTH '*' FROM e.type_a), '*', n.n), '*', -1)

The UNION (without the ALL keyword) of this construct will give us the list of distinct color values and with GROUP BY id and GROUP_CONCAT we'll get the * separated value list. Last we add a leading and a trailing * to match your requirements.

For the update you've got to modificate the select, so that it returns just one column with one row (with the where clause).

Note

As stated by peterm this will allow up to 100 values in your value list. I don't believe you will need more, but if you will, then you've got to adapt the generating of the numbers up to your needs.

Tested and worked like a charm! Great, Vmai! You saved me hours of work! You deserve a dinner! Thanks! — DavidF, Jul 30 '14 at 10:06

Merge two colums, multiple rows ignoring duplicates - MySQL

1 Answers1

Linked