54 lines
1.4 KiB
Plaintext
54 lines
1.4 KiB
Plaintext
DELIMITER //
|
|
|
|
CREATE OR REPLACE PROCEDURE gp_split(
|
|
p_uuid VARCHAR(100),
|
|
p_source TEXT,
|
|
p_split VARCHAR(2)
|
|
)
|
|
/*
|
|
function : 將傳入的字串, 依 p_split 切割為多筆資料, 傳回暫存 table
|
|
description:
|
|
呼叫範例:
|
|
SET p_uuid = UUID();
|
|
CALL gp_split(p_uuid, 'R,G,B',',');
|
|
select col from _split where guid = p_uuid and rowid = 1;
|
|
DELETE FROM _split WHERE guid = p_uuid;
|
|
|
|
必須要有 CREATE TABLE _split( guid varchar(100), rowid INT, col varchar(600) );
|
|
|
|
https://www.sqlines.com/sql-server-to-mariadb/returns_table
|
|
build date : 2023/11/20
|
|
|
|
modify history
|
|
item who date modify docs
|
|
==== ========== ========== ================================================
|
|
1 michael 2011/09/08 新增
|
|
2 Michael 2025/07/11 改寫為MariaDB版本並優化
|
|
*/
|
|
BEGIN
|
|
DECLARE v_pos INT;
|
|
DECLARE v_cnt INT;
|
|
DECLARE v_length INT;
|
|
DECLARE v_temp TEXT;
|
|
|
|
SET v_length = LENGTH(p_split);
|
|
SET v_temp = p_source;
|
|
SET v_cnt = 1;
|
|
|
|
-- 循環處理分割
|
|
WHILE LOCATE(p_split, v_temp) > 0 DO
|
|
SET v_pos = LOCATE(p_split, v_temp);
|
|
INSERT INTO _split (guid, rowid, col) VALUES (p_uuid, v_cnt, SUBSTRING(v_temp, 1, v_pos - 1));
|
|
|
|
SET v_temp = SUBSTRING(v_temp, v_pos + v_length);
|
|
SET v_cnt = v_cnt + 1;
|
|
END WHILE;
|
|
|
|
-- 添加最後一部分
|
|
IF LENGTH(v_temp) > 0 THEN
|
|
INSERT INTO _split(guid, rowid, col) VALUES (p_uuid, v_cnt, v_temp);
|
|
END IF;
|
|
|
|
END //
|
|
|
|
DELIMITER ; |