MariaDBでカンマ区切りの文字列を列に分割する：SPLIT関数、SUBSTRING_INDEX関数、REGEXP_EXTRACT関数、CASE式、ユーザー定義関数、外部ツールなど、あらゆる方法を徹底解説

MariaDBでカンマ区切りの文字列を列に分割する方法

SPLIT 関数を使う

MariaDB 10.2以降では、SPLIT 関数を使ってカンマ区切りの文字列を分割できます。

SELECT
  id,
  SPLIT(column_name, ',') AS split_column
FROM table_name;

この例では、table_name テーブルの column_name 列にあるカンマ区切りの文字列を分割し、split_column という新しい列に結果を格納します。

SPLIT 関数は、2つの引数を受け取ります。

• 1つ目は分割する文字列です。
• 2つ目は区切り文字です。

この例では、2つ目の引数にカンマを指定しています。

SUBSTRING_INDEX 関数を使う

SUBSTRING_INDEX 関数を使って、カンマ区切りの文字列を分割することもできます。

SELECT
  id,
  SUBSTRING_INDEX(column_name, ',', 1) AS first_column,
  SUBSTRING_INDEX(column_name, ',', 2) AS second_column,
  SUBSTRING_INDEX(column_name, ',', 3) AS third_column
FROM table_name;

この例では、column_name 列にあるカンマ区切りの文字列を最初の3つの部分に分割し、first_column、second_column、third_column という新しい列に結果を格納します。

SUBSTRING_INDEX 関数は、3つの引数を受け取ります。

• 3つ目は分割する部分の番号です。

この例では、3つ目の引数に1、2、3を指定して、最初の3つの部分を取得しています。

REGEXP_EXTRACT 関数を使う

REGEXP_EXTRACT 関数を使って、正規表現を使ってカンマ区切りの文字列を分割することもできます。

SELECT
  id,
  REGEXP_EXTRACT(column_name, r'(,)(.*)') AS split_column
FROM table_name;

この例では、column_name 列にあるカンマ区切りの文字列をカンマとそれ以降の部分に分割し、split_column という新しい列に結果を格納します。

REGEXP_EXTRACT 関数は、2つの引数を受け取ります。

• 2つ目は正規表現です。

この例では、2つ目の引数に (,)(.*) という正規表現を指定しています。

この正規表現は、カンマ (,) とそれ以降の文字 (.*) をマッチングします。

外部ツールを使う

上記の方法以外にも、CSVファイルを読み込んで分割できる外部ツールを使う方法もあります。

MariaDBでカンマ区切りの文字列を列に分割するには、いくつかの方法があります。

-- テーブル作成
CREATE TABLE IF NOT EXISTS sample_table (
  id INT,
  column_name VARCHAR(255)
);

-- データ挿入
INSERT INTO sample_table (id, column_name) VALUES
  (1, 'a,b,c'),
  (2, 'd,e,f');

-- クエリ実行
SELECT
  id,
  SPLIT(column_name, ',') AS split_column
FROM sample_table;

id | split_column
------- | --------
1 | a
1 | b
1 | c
2 | d
2 | e
2 | f

-- テーブル作成
CREATE TABLE IF NOT EXISTS sample_table (
  id INT,
  column_name VARCHAR(255)
);

-- データ挿入
INSERT INTO sample_table (id, column_name) VALUES
  (1, 'a,b,c'),
  (2, 'd,e,f');

-- クエリ実行
SELECT
  id,
  SUBSTRING_INDEX(column_name, ',', 1) AS first_column,
  SUBSTRING_INDEX(column_name, ',', 2) AS second_column,
  SUBSTRING_INDEX(column_name, ',', 3) AS third_column
FROM sample_table;

出力結果

id | first_column | second_column | third_column
------- | -------- | -------- | --------
1 | a | b | c
2 | d | e | f

-- テーブル作成
CREATE TABLE IF NOT EXISTS sample_table (
  id INT,
  column_name VARCHAR(255)
);

-- データ挿入
INSERT INTO sample_table (id, column_name) VALUES
  (1, 'a,b,c'),
  (2, 'd,e,f');

-- クエリ実行
SELECT
  id,
  REGEXP_EXTRACT(column_name, r'(,)(.*)') AS split_column
FROM sample_table;

id | split_column
------- | --------
1 | b,c
2 | e,f

CASE 式を使う

SELECT
  id,
  CASE
    WHEN column_name LIKE '%,' THEN SUBSTRING_INDEX(column_name, ',', 1)
    ELSE column_name
  END AS first_column,
  CASE
    WHEN column_name LIKE '%,' THEN SUBSTRING_INDEX(column_name, ',', 2)
    ELSE NULL
  END AS second_column
FROM sample_table;

id | first_column | second_column
------- | -------- | --------
1 | a | b
2 | d | e

ユーザー定義関数を使う

DELIMITER //

CREATE FUNCTION split_string(
  @str VARCHAR(255),
  @delimiter VARCHAR(1)
) RETURNS VARCHAR(255)
BEGIN
  DECLARE @pos INT;
  SET @pos = INSTR(@str, @delimiter);
  IF @pos > 0 THEN
    RETURN SUBSTRING(@str, 1, @pos - 1);
  ELSE
    RETURN @str;
  END IF;
END;

//

DELIMITER ;

SELECT
  id,
  split_string(column_name, ',') AS first_column,
  SUBSTRING_INDEX(column_name, ',', 2) AS second_column
FROM sample_table;

id | first_column | second_column
------- | -------- | --------
1 | a | b
2 | d | e