[MySQL] Concat string, Split string
* Concat strings with a delimiter
GROUP_CONCAT
1
2
3
4
5
6
7
8
9
| 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');
|
1
2
| 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)
1
2
| 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)
1
2
| SELECT GROUP_CONCAT(CITY SEPARATOR '$') AS ALL_CITY_NAMES
FROM group_concat_test;
|
+-------------------------------+
| ALL_CITY_NAMES |
+-------------------------------+
| London$Luxembourg$Paris$Seoul |
+-------------------------------+
1 row in set (0.00 sec)
1
| DROP TABLE IF EXISTS group_concat_test;
|
* Split string array using a specific delimiter
# Create a procedure, Call a procedure
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
| 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 ;
|
1
| 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)
1
| 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)
But they shaped a partnership with Par-A-Dice Casino which gained them entry into the lucrative and more and more crowded Illinois betting market. A 2020 examine of Kenyan pupil bettors by Ogachi diagnosed practically seven out of 10 with playing disorders. Nelson Bwire, who led the Kenyatta University poll while an undergraduate, was so alarmed he based a nonprofit, the Gaming Awareness Society of Kenya, that seeks 우리카지노 to scale back back} playing hurt. Bwire has counseled students who’ve been pressured to leave school after betting away their tuition, and workers who’ve been jailed for squandering their employers’ money. Nearly $60 million has been spent — much of it by massive Indigenous tribes — to support campaigns for Proposition 26, which, if enacted, will allow only tribal casinos to supply in-person betting. California casinos make up much of the $50 million marketing campaign towards Prop 26.
ReplyDelete