【MySQL】特定の文字で文字列を分割する
概要
データベースにおいて、一つのカラム内に文字列が入っており、特定の文字列で分割したいケースがたまにあります。今回はMySQLで特定の文字列を境に分割する方法についてメモします。
環境
- MySQL 5.5.62
準備
以下のようなSQLでテスト用のデータを作成します。
CREATE TABLE testdb.sltest(
col1 varchar(10)
);
INSERT INTO testdb.sltest (col1) VALUES ('I am');
INSERT INTO testdb.sltest (col1) VALUES ('He is');
INSERT INTO testdb.sltest (col1) VALUES ('They are');
結果、以下のようなテーブルとデータが出来上がります。
col1 |
---|
I am |
He is |
They are |
今回は文字列の間に半角スペースを入れています。これを分割していきます。
SUBSTRINGとLOCATEの組み合わせで分割
SUBSTRINGは文字列の抽出ができる関数です。一方、LOCATEは特定の文字の位置を返すことができる関数です。この二つを組み合わせることで、特定の文字を見つけ出して分割することができます。
それぞれの関数の引数は以下の通り
- SUBSTRING(カラム名, 抽出開始文字位置, 抽出終了文字位置)
- LOCATE(特定文字, カラム名)
組み合わせた例は以下のようなSQLです。
SELECT
col1
,SUBSTRING(col1,1,LOCATE(' ',col1)-1) AS col2
,SUBSTRING(col1,LOCATE(' ',col1)+1,LENGTH(col1)) AS col3
FROM
testdb.sltest
この結果は以下のようになります。主語と述語がうまく分割され、別カラムとして抽出できました。
col1 | col2 | col3 |
---|---|---|
I am | I | am |
He is | He | is |
They are | They | are |
今回は間の空白が不要だったため、LOCATE(‘ ‘,col1)-1とLOCATE(‘ ‘,col1)+1のように±1で文字位置を調整していますが、残したい場合はこの値を0にしてうまく調整するとどちらかに残すことができます。
以上