MySQL 分割文字列を行に

1. MySQL で文字列を行に分割する
2. SUBSTRING_INDEX() メソッドを使用して文字列を行に分割する
3. まとめ

MySQL はテーブルの列と行にデータを格納し、ユーザーはそれらのデータを定義、制御、操作、およびクエリできます。 MySQL はさまざまな機能を提供します。そのうちの 1つの機能は、文字列を行に分割することです。

この記事では、文字列を行に分割する方法、その使用法、および MySQL でそれを実行する方法について説明します。

MySQL で文字列を行に分割する

データベース内のデータを操作するとき、文字列を操作することがあります。 たとえば、表には、顧客の氏名、住所、およびいくつかの説明を含めることができます。

場合によっては、これらのデータを文字列として追加し、さまざまな目的で行に分割する必要がある場合があります。 この目標を達成するために、MySQL はメソッド SUBSTRING_INDEX() を提供します。

SUBSTRING_INDEX() メソッドを使用して文字列を行に分割する

SUBSTRING_INDEX() は、文字列から部分文字列を導出するために MySQL が提供する機能です。 特定の区切り文字をチェックするため、その前の部分文字列が出力されます。

構文は次のとおりです。

SUBSTRING_INDEX(string, delimiter, number);

上記の構文には、3つのパラメーターがあります。 string はサブストリングを導出するために入力した行を参照し、delimiter は関数が検索する値です。

区切り文字が検索される回数は number と呼ばれます。 number は、正の数または負の数のいずれかです。

正の数の場合は、文字列の先頭から末尾まで、区切り文字までの部分文字列を取得します。 負の数の場合は、区切り文字までの部分文字列を取得し、文字列の後ろから前に区切り文字の検索を開始します。

例を試してみましょう。

SELECT SUBSTRING_INDEX('England, America, Japan, China',',',2) AS newp;

上記のように、コンマで区切られた 4つの国を含む文字列を指定しました。 次に、区切り文字としてカンマを渡し、数字として 2つ割り当てました。

結果を見てみましょう。

出力：

ご覧のとおり、数値として 2 を指定したため、サブストリングは 2 番目のコンマまで残ります。 number は負の数で、これをもう一度試してみましょう。

SELECT SUBSTRING_INDEX('England, America, Japan, China',',',-1) AS newp;

以下は上記のコードの結果です。ご覧のとおり、出力として China が得られます。これは、数値として -1 を指定したためです。これは負の数値であるため、最後の区切り文字を検索し、正しい部分文字列を出力しました。 それに。

出力：

それでは、このメソッドを使用して文字列を行に分割する方法を見てみましょう。 以下の例を参照してください。

DELIMITER //

-- Creating a procedure
CREATE procedure newProcedure()

BEGIN
-- Declaring a variable as myvar to store the string
DECLARE myvar varchar (300);

-- Assigning the string to the variable
SET myvar = 'China.Japan.USA';

-- Deriving the substrings
(SELECT SUBSTRING_INDEX(myvar, '.', 1) AS Countries)
UNION
(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(myvar, '.', 2),'.','-1') AS Countries)
UNION
(SELECT SUBSTRING_INDEX(myvar, '.', -1) AS Countries);
END //
DELIMITER ;

-- Calling the procedure
CALL newProcedure()

上記のコードでは、newProcedure というプロシージャを作成し、文字列を格納する変数を宣言しています。 文字列にはピリオド記号が含まれているため、これが区切り文字になります。

SUBSTING_INDEX メソッドを 4 回 (中央の部分文字列を取得するために途中で 2 回) 使用し、UNION を使用してこれらの部分文字列を 1つの列に配置しました。 最後に、プロシージャを呼び出しました。出力は次のとおりです。

出力：

文字列を行に分割しましたが、ピリオド記号が 3つ以上ある場合、このコードは困難になります。

この問題を克服するために、ソリューションを構築できます。 まず、国名を含む単一の列を持つテーブルを作成できます。

フルネームを分割する必要があるため、追加する国名には複数の名前があります。 次のように、3つの国名を持つテーブルを作成してみましょう。

CREATE TABLE countries (countryName VARCHAR(100));
INSERT INTO countries VALUES
('United States America'),
('New Zeland'),
('United Kingdom');

次に、データとして 1、2、3 を含む numberList という別のテーブルを作成しましょう。

CREATE TABLE numberList (indexing INT);
INSERT INTO numberList VALUES (1),(2),(3);

このテーブルの目的は、分割する部分文字列の最大数を設定することです。 3つの数字を追加しました。コードが実行されると、行内に 4つの部分文字列がある場合でも、文字列が最大 3つの部分文字列に分割されます。

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(countries.countryName, ' ', numberList.indexing), ' ', -1) countryName
FROM numberList
INNER JOIN countries
ON CHAR_LENGTH (countries.countryName) -CHAR_LENGTH(REPLACE(countries.countryName, ' ', '')) >= numberList.indexing - 1

上記のコード チャンクでは、SUBSTRING_INDEX 関数を使用しており、その中に、国名がスペースで区切られているため、検索する区切り文字として配置しています。 次に、国名に 3つ以上の部分文字列があるかどうかを確認しました。

その後、部分文字列を行に出力しました。

完全なコード:

-- Creating the table
CREATE TABLE countries (countryName VARCHAR(100));

-- Inserting values
INSERT INTO countries VALUES
('United States America'),
('New Zeland'),
('United Kingdom');

-- Create a table that contains the numbers
CREATE TABLE numberList (indexing INT);
INSERT INTO numberList VALUES (1),(2),(3);

-- Deriving the substrings
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(countries.countryName, ' ', numberList.indexing), ' ', -1) countryName
FROM numberList
INNER JOIN countries
ON CHAR_LENGTH (countries.countryName) -CHAR_LENGTH(REPLACE(countries.countryName, ' ', '')) >= numberList.indexing - 1

以下は、コードを実行した後の結果です。

出力：

ご覧のとおり、予想どおり、国名は部分文字列に分割されています。

まとめ

この記事では、MySQL について簡単に紹介しました。 次に、文字列を行に分割する必要がある理由と、MySQL でそれを実行する方法を学びました。

目標を達成するために、MySQL は SUBSTRING_INDEX メソッドを提供し、それを使用してタスクを完了する方法を調べました。

STRING_SPLIT 関数など、このタスクを実行する方法は他にもありますが、この記事で説明した手法が MySQL での最良の方法です。