Split comma delimited string with array values

Question

I wanted to replace a data from release db to test db with prefix(each store in storeList array).

I'm getting error like "Unknown system variable 'store'"

But I don't know why this error occurs.

DELIMITER
CREATE PROCEDURE `replace_release_to_test_db`(baseUrl VARCHAR(255),     substituteDomain VARCHAR(255), IN storeList VARCHAR(255))
BEGIN
   DECLARE pos int;
   SET storeList = LTRIM(RTRIM(storeList))+ ',';
   SET pos = SUBSTRING_INDEX(storeList,',', 1);
        WHILE pos > 0 do
            SET store = LEFT(storeList, pos - 1);
            IF store <> '' THEN
                SET oldDomain = CONCAT(store,".",baseUrl);
                SET newDomain = CONCAT(substituteDomain,"-",store,".",baseUrl);
                UPDATE core_config_data SET value = REPLACE(value, oldDomain, newDomain);
            END IF
            SET storeList = RIGHT(storeList, LEN(storeList) - pos);
            SET pos = SUBSTRING_INDEX(storeList,',', 1);
        END WHILE;

        UPDATE core_config_data SET value = REPLACE(value, CONCAT("http://",baseUrl), CONCAT("http://"substituteDomain,".",baseUrl));
        UPDATE core_config_data SET value = REPLACE(value, CONCAT("https://",baseUrl), CONCAT("https://"substituteDomain,".",baseUrl));

        SELECT * FROM core_config_data WHERE value LIKE '%abc.kr%';
END
DELIMITER;

CALL replace_release_to_test_db("abc.kr","test", "my,sg,ph,id,th,us,my-mobile,sg-mobile,ph-mobile,id-mobile,th-mobile,us-mobile")

You didn't declare store yet use SET store = LEFT(storeList, pos - 1);. Same applies to storeList... and BTW, to concatenate strings in MySQL use the concat() function not + in SET storeList = LTRIM(RTRIM(storeList))+ ',';. And apart from all that: Just don't use comma separated strings, use table rows. It will make life a lot easier. — sticky bit
– sticky bit, Commented May 31, 2018 at 16:27

Uueerdo · Accepted Answer · 2018-05-31 16:25:40Z

1

You have SET store = LEFT(storeList, pos - 1); (and IF store ... and so on) but you have not declared a variable named store.

answered May 31, 2018 at 16:25

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Split comma delimited string with array values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related