##1. Concat String with delimiter
GROUP_CONCAT
https://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html
ex.
DROP TABLE IF EXISTS group_concat_test;
CREATE TABLE group_concat_test
(
SEQ INT NOT NULL AUTO_INCREMENT,
CITY VARCHAR(20),
PRIMARY KEY `pk_group_concat_test` (SEQ)
) ENGINE = MEMORY;
INSERT INTO group_concat_test (CITY) VALUES ('Luxembourg') ,('London'), ('Seoul'), ('Paris');
SELECT GROUP_CONCAT(CITY) AS ALL_CITY_NAMES
FROM group_concat_test;
+-------------------------------+
| ALL_CITY_NAMES |
+-------------------------------+
| Luxembourg,London,Seoul,Paris |
+-------------------------------+
1 row in set (0.00 sec)
SELECT GROUP_CONCAT(CITY SEPARATOR '#') AS ALL_CITY_NAMES
FROM group_concat_test;
+-------------------------------+
| ALL_CITY_NAMES |
+-------------------------------+
| Luxembourg#London#Seoul#Paris |
+-------------------------------+
1 row in set (0.00 sec)
SELECT GROUP_CONCAT(CITY ORDER BY CITY SEPARATOR '$') AS ALL_CITY_NAMES
FROM group_concat_test;
+-------------------------------+
| ALL_CITY_NAMES |
+-------------------------------+
| London$Luxembourg$Paris$Seoul |
+-------------------------------+
1 row in set (0.00 sec)
DROP TABLE IF EXISTS group_concat_test;
##2. Split String Array using specific delimiter
#Create a procedure and call.
DELIMITER $$
DROP PROCEDURE IF EXISTS usp_split_array_test$$
CREATE PROCEDURE usp_split_array_test(
iValueArray VARCHAR(100) # Input array value ex) 'value1, value2, value3'
,iDelimiter CHAR(1) # Delimiter
)
BEGIN
DECLARE vValueArry VARCHAR(100);
DECLARE vValue VARCHAR(10);
DECLARE vDelimiter CHAR(1);
SET vDelimiter := iDelimiter; #Variable for delimiter
IF IFNULL(vDelimiter,'') = '' THEN
SET vDelimiter := ',';
END IF;
SET vValueArry := iValueArray; #Variable for split work
IF RIGHT(vValueArry,1) != vDelimiter THEN
SET vValueArry := CONCAT(vValueArry,vDelimiter); # 'value1, value2, value3' --> 'value1, value2, value3,'
END IF;
DROP TEMPORARY TABLE IF EXISTS temp_split_array;
CREATE TEMPORARY TABLE temp_split_array
(
SEQ INT NOT NULL AUTO_INCREMENT,
VALUE VARCHAR(20),
PRIMARY KEY `PK_temp_split_array` (SEQ)
) ENGINE = MEMORY;
#SET @vValueArry := 'value1, value2, value3,'
WHILE (LOCATE(vDelimiter, vValueArry) > 0)
DO
SET vValue := LEFT(vValueArry, LOCATE(vDelimiter,vValueArry) - 1);
SET vValueArry := SUBSTRING(vValueArry, LOCATE(vDelimiter,vValueArry) + 1);
INSERT INTO temp_split_array (SEQ, VALUE)
VALUES (null, vValue);
END WHILE;
SELECT * FROM temp_split_array;
DROP TEMPORARY TABLE IF EXISTS temp_split_array;
END$$
DELIMITER ;
CALL usp_split_array_test('Luxembourg,London,Seoul,Paris', ',');
+-----+------------+
| SEQ | VALUE |
+-----+------------+
| 1 | Luxembourg |
| 2 | London |
| 3 | Seoul |
| 4 | Paris |
+-----+------------+
4 rows in set (0.00 sec)
CALL usp_split_array_test('Luxembourg;London;Seoul;Paris', ';');
+-----+------------+
| SEQ | VALUE |
+-----+------------+
| 1 | Luxembourg |
| 2 | London |
| 3 | Seoul |
| 4 | Paris |
+-----+------------+
4 rows in set (0.01 sec)