How to separate a string and re build it

Question

Separating String list and replacing same list with new values in mysql I have following data in my table_1 Table table_1(Currently saved structure)

code        value
12_A        ["A","B","C","D"]  
12_B        ["E","F","G","H"]
12_3        ["I","J","K","L"]

But each code have different values with different description. like::

code    value     description
12_A    A         Apple
12_A    B         Ball
12_A    C         Cat
12_A    D         Dog
12_B    E         Eagle
12_B    F         Flag
.       .         .
.       .         . 
.       .         .

I have to Separate the value list from table_1 and need to save again in same table i.e table_1(in this structure)::

code            value
12_A            ["Apple","Ball","Cat","Dog"]
12_B            ["Eagle","Flag",.......]
12_3            [......................] 

Answer 1

You can use GROUP_CONCAT() :

UPDATE Table1 s
SET s.value = (SELECT t.code,CONCAT('["',
                           GROUP_CONCAT(t.description ORDER BY t.description SEPARATOR '","'),
                           ']') 
             FROM Table_With_val t
             WHERE t.code = s.code
               AND s.value LIKE CONCAT('%"',t.value,'"%'))

You didn't provide any conclusive information, I assumed the second data sample you provided is an existing table, and table1 is the table you want to update.

NOTE: This is a bad DB structure! it would most defiantly cause problem in the future especially when required to make joins . I strongly advise you to normalize your data and store each description and value in its own record.

Answer 2

you can create a function in which you can pass your string list as parameter in case of your example ["A","B","C","D"] will be the parameter. The function will break down the string and will concatenate the descriptions according. The example of the function you can use is given below:

DELIMITER $$

DROP FUNCTION IF EXISTS codeToDesc$$

CREATE FUNCTION codeToDesc(commaSeperatedCodeList TEXT) RETURNS TEXT CHARSET utf8
BEGIN
DECLARE finalString TEXT;
DECLARE inputCodeList TEXT;
DECLARE codeName VARCHAR(255);
DECLARE codecount BIGINT(5);

SET finalString='';
SET inputCodeList = REPLACE(REPLACE(REPLACE(commaSeperatedCodeList,'[',''),']',''),'"','');

DROP TEMPORARY TABLE IF EXISTS test.code_table;
DROP TEMPORARY TABLE IF EXISTS test.code_count;
CREATE TEMPORARY TABLE test.code_table (CODE VARCHAR(255));
CREATE TEMPORARY TABLE test.code_count (countNo BIGINT(11));

INSERT INTO test.code_count(countNo) SELECT(LENGTH(inputCodeList)-LENGTH(REPLACE(inputCodeList,',','')) + 1);

BEGIN

    DECLARE table_cursor CURSOR FOR SELECT countNo FROM test.code_count;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET codecount = (SELECT countNo FROM test.code_count ORDER BY countNo ASC LIMIT 1);

    OPEN table_cursor;
    readLoop1: LOOP

    FETCH table_cursor INTO codecount;  

        IF codecount=0 THEN
              LEAVE readLoop1;
        END IF; 

    SET codeName=(SELECT SUBSTRING_INDEX(inputCodeList,',',1)); 
    INSERT INTO test.code_table(CODE) SELECT codeName;
    SET inputCodeList=(SELECT TRIM(BOTH ',' FROM REPLACE(inputCodeList,codeName,'')));  
    INSERT INTO test.code_count(countNo) SELECT codecount-1;    
    SET codeName='';

    END LOOP;
    CLOSE table_cursor;
END;    

    -- use your code and description here, i guess those should be fixed
    SELECT CONCAT('["',REPLACE(GROUP_CONCAT(CASE WHEN CODE='A' THEN 'Apple'
               WHEN CODE = 'B' THEN 'Ball'
               WHEN CODE = 'C' THEN 'Cat'
               WHEN CODE = 'D' THEN 'Dog'
               ELSE '' END),',','","'),'"]') INTO finalString FROM test.code_table;

    RETURN finalString; 

END$$

DELIMITER ;

Try this, let me know if you any issue occurred.