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 ;