D. Aryal D. Aryal - 3 months ago 7
MySQL Question

How to separate a string and re build it

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

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.