[MySQL] Sql Serverの STRING_SPLIT ようなものをMySQLで実装する方法

inno
2022-11-09 15:21 171 0

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');

コメント