SQL SERVERには「STRING_SPLIT」という便利なものがあるが、MySQLには存在していない。
STRING_SPLITのような処理をMySQLでできる方法を説明する。
方法は2つある。
カンマ区切りを前提とする。
方法1.json_tableを利用する。
例)文字列の場合
SET @str = 'D,A,B,C';
-- 置換
SET @str = REPLACE(@str, ',', '","');
-- 前後に[]を付ける
SET @strVal = CONCAT('["', @str, '"]');
SELECT *
FROM json_table(
@strVal
, '$[*]' columns( RtnVal varchar(100) path '$' )
) AS A;
例)数字の場合
SET @str = '5,2,1,4,3,6';
-- 前後に[]を付ける
SET @strVal = CONCAT('[', @str, ']');
SELECT *
FROM json_table(
@strVal
, '$[*]' columns( RtnVal int path '$' )
) AS A;
方法2.
まず、「fnSplit」関数を作成する。
drop FUNCTION if exists fnSplit;
DELIMITER //
CREATE FUNCTION fnSplit(
strval VARCHAR(2000),
delim VARCHAR(12),
pos INT
)
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(strval, delim, pos),
LENGTH(SUBSTRING_INDEX(strval, delim, pos -1)) + 1),
delim, '');
END;
//
DELIMITER ;
次はストアドプロシージャを作成してloopなどを利用して使う。
Drop PROCEDURE IF EXISTS pTest_STRING_SPLIT;
DELIMITER //
CREATE PROCEDURE pTest_STRING_SPLIT
(
IN prmSortArray varchar(1000)
)
BEGIN
DECLARE i int;
DECLARE rsStr varchar(100);
CREATE TEMPORARY TABLE tempData
(
sn int not null AUTO_INCREMENT,
str varchar(100),
PRIMARY KEY (sn)
);
set i = 1;
loop1: LOOP
SET rsStr = fnSplit(prmSortArray, ',', i);
IF LENGTH(rsStr) <= 0 THEN
LEAVE loop1;
ELSE
BEGIN
INSERT INTO tempData(str)
VALUE (rsStr);
SET i = i + 1;
END;
END IF;
END LOOP loop1;
SELECT * FROM tempData ORDER BY sn ASC;
DROP TEMPORARY TABLE IF EXISTS tempData;
END;
//
DELIMITER ;
実行してみる。
-- 数字
CALL pTest_STRING_SPLIT('5,1,2,3');
-- 文字列
CALL pTest_STRING_SPLIT('D,A,B,C');