データの切り出しに便利なsubstringを、SQL文を交えて徹底解説

SQL文には「select」「update」「insert」「delete」といった基本的なもののほかに、substringも用意されています。substringは直接データを挿入したり、消したり、更新したりするものではありません。代わりに文字列の一部を切り出す働きがあり、SQLを効果的に活用するうえで重要な役割を果たしています。

substringはどのような特徴を持っているのでしょうか。この記事で詳しく確認していきましょう。

substringとはなにか？

substringはデータを扱ううえで、便利な機能を数多く持っています。この記事ではsubstringが持つ3つの特徴を解説します。どのようなものなのか、確認していきましょう。

文字列を切り出す関数

substringは、文字列から必要な部分を切り出す関数です。前方一致や後方一致、特定の文字列が含まれているかなどのチェックに活用できます。構文を以下に示しました。

SUBSTRING(文字列、開始位置、切り出す文字数)

切り出し対象となる文字列に加えて、どこから何文字分を切り出すかという情報があれば、文字列の切り出しが可能です。一例として、文字列「abcdefg」の先頭から4文字を返すSQL文を以下に示しました。

SELECT SUBSTRING('abcdefg',1,4);

実行すると、abcdが結果として返されます。「文字列」の部分は、シングルクォーテーションで囲む必要があることに注意してください。

数値からの切り出しも可能

切り出し対象の文字列は、数値でもかまいません。以下のSQL文を見ていきましょう。

SELECT SUBSTRING(1234567890,1,4);

実行すると、1234が表示されます。数値の場合、値をシングルクォーテーションで囲む必要はありません。

切り出した数値どうしを計算することも可能です。例として、以下の計算式をみていきましょう。

SELECT SUBSTRING(1234567890,1,4)+SUBSTRING(1234567890,1,3);

これは1234に123を加えることと同じです。結果は1357となります。

使い方はデータベースにより異なる場合がある

substringの文法は、RDBMSやバージョンにより多少異なる場合があることに注意してください。例えばOracleの場合は、以下のとおりとなります。

・文字数で長さを示したい場合は「SUBSTR」

・バイト数で長さを示したい場合は「SUBSTRB」

substringに限らず、初めて関数を使う場合は事前にマニュアルなどを参照し、正しい使い方をマスターしておきましょう。

目的別・substringの基本的な5つの使い方

substringの基本的な使い方は、5つあります。ここからはMySQL8.0を用いて、どのようなSQL文を書けば良いか、またどのような結果が得られるのか解説していきましょう。

この章では後半部分で、「果物」表を使います。表に含まれる項目とデータを、以下に示しました。

先頭から何文字か切り出したい

先頭から何文字か切り出したい場合は、以下の構文を使いましょう。

SUBSTRING(文字列または数値, 1, 切り出す文字数)

例えば「けんこうかんり」という語句の先頭から4文字目までを切り出したいときは、以下のSQL文を実行します。

SELECT SUBSTRING('けんこうかんり',1,4);

実行すると「けんこう」が返されます。この例は日本語の文字で試しましたが、「けんこうかんり」の部分を「alphabet」などの英単語に置き換えた場合でも、問題なく4文字目まで切り出せます。

末尾から何文字か切り出したい

業務によっては、末尾からの文字数をチェックしたい場合もあります。実現する方法は、2通りに分かれます。例えば「5文字目から末尾まで切り出したい」という場合は、以下の構文を使いましょう。

SUBSTRING(文字列 FROM 開始位置)

上記の構文は切り出し始める文字の位置を指定し、そのあとの文字をすべて切り出す方法です。出力される文字数は、元の文字列により異なります。

ここで「abcdefg」という文字列を例に、5文字目から後ろを切り出すSQL文を確認していきましょう。

SELECT SUBSTRING('abcdefg' FROM 5);

5文字目は「e」となるため、実行すると「efg」が表示されます。

一方で元の文字数に関わらず、末尾から切り出す文字数を一定にしたい場合はどうしたらよいのでしょうか。この場合は、以下の構文を使います。

SUBSTRING(文字列, -(切り出す文字数))

マイナスがついているため、切り出しは末尾から行なわれます。例えば「abcdefg」という文字列に対し、後ろ3文字を切り出す構文を確認しましょう。

SELECT SUBSTRING('abcdefg', -3);

「efg」が結果として表示されます。

途中の文字列を切り出したい

substringでは、単語や語句の途中に含まれる文字列も切り出せます。この操作は、以下の構文で行なえます。どちらを使っても、得られる結果は同じです。

SUBSTRING(文字列  FROM 開始位置 FOR 文字列の長さ)
SUBSTRING(文字列、開始位置、文字列の長さ)

一例として、文字列「abcdefg」の3文字目と4文字目を切り出すSQL文を確認していきましょう。

SELECT SUBSTRING('abcdefg' FROM 3 FOR 2);
SELECT SUBSTRING('abcdefg',3,2);

このSQL文は、「先頭から数えて3文字目から2文字分を切り出す」という意味です。結果として3文字目と4文字目を切り出すSQL文となり、どちらを実行した場合でもcdが表示されます。

開始位置を負の整数で指定した場合は、末尾からの文字数となります。以下のSQL文をみていきましょう。開始位置の「3」が「-3」に変わった以外は、さきほどと同一のSQL文です。

SELECT SUBSTRING('abcdefg' FROM -3 FOR 2);

このSQL文は、「末尾から数えて3文字目から2文字分を切り出す」という意味です。そのため、実行するとefが表示されます。

指定した列に格納された文字列の一部を切り出す

ここまで解説した構文のうち、文字列の部分は変数に代えることもできます。これを利用し、表の指定された列に格納された文字列の一部を切り出すことも可能です。

さきに紹介した「果物」表の「商品」列について、末尾が「特選」で終わる商品を検索してみましょう。

SELECT * FROM 果物 WHERE SUBSTRING(商品, -2, 2)='特選';

これはSUBSTRING(文字列、開始位置、文字列の長さ)構文を応用したものです。「商品」列は変数として使っているため、シングルクォーテーションで囲んでいないことに注目してください。また開始位置にマイナスを指定しているため、末尾2文字に「特選」が含まれる商品が選ばれます。

実行した結果、末尾に「特選」がつく商品が表示されました。

SQL文のさまざまな場所で使用可能

substringは、SQL文のさまざまな場所で使えます。これまで解説したSQL文は、代表的な例です。SELECT文やWHERE句のなかだけでなく、UPDATE文のなかでも使用可能です。

このため「いったんsubstringで切り出した文字列を変数に格納して、その変数を使って比較したりデータを更新したりする」といった手間は必要ありません。切り出し元の文字列からダイレクトに必要な部分を切り出し、活用できることが魅力です。

substringの効果的な使い方3選

substringはうまく使うことで、処理を効率よく行なえます。ここからはsubstringが役立つ3つのケースを取り上げ、便利さを確認していきましょう。

指定した文字列が含まれるデータを抽出する

ここまで解説したとおり、substringは前方一致や後方一致など、指定した文字列が含まれるデータの抽出に活用できます。加えて語句の途中に含まれる文字列をもとに、必要なデータを抽出できる場合もあります。

「果物」表の「商品」列は、「フルーツ名」の末尾に「普通」または「特選」の文字がつくというルールです。このことを利用し、「フルーツ」が含まれるデータを検索してみましょう。

SELECT * FROM 果物 WHERE SUBSTRING(商品, -6, 4)='フルーツ';

上のSQL文では、末尾から数えて6番目にあたる文字から3番目にあたる文字までが「フルーツ」であるデータを表示します。結果は以下のとおりです。

キウイフルーツとグレープフルーツの行が表示されました。

なおsubstringでは「語句中のどこかに、指定された文字列が含まれるデータ」といった抽出方法には対応していません。

数値の一部を切り取って計算する

substringでは数値の一部分を切り取り、計算に使うことも可能です。例として、以下のケースを考えてみましょう。

同一の果物を5個買いたいお客さんがいる。この方は、5円や10円といった硬貨をあまり持ちたくない。できるだけお釣りのないように支払いたいので、5円や10円といった端数が出るかどうか事前に確認したい。

このような要件には末尾2桁を切り取り、個数をかけて端数を確認する方法が有効です。以下のSQL文で実現できます。

SELECT 番号, 商品, SUBSTRING(単価, -2, 2)*5 FROM 果物;

以下の結果が表示されました。

パイナップルであれば、5円や10円は不要です。その他のフルーツを5個買う場合は、10円硬貨が必要です。なかでも「キウイフルーツ普通」や「パパイヤ特選」を5個買う場合は、5円硬貨も用意しなければなりません。

種類別に金額を集計する

あらかじめデータの先頭や末尾に種類を示す情報を入れていれば、substring関数で種類別に集計できます。ただし「種類を示す情報」の文字数がすべて同じことが条件です。

「果物」表には商品名の末尾に、「普通」または「特選」といった等級を示す語句が含まれています。同じ等級の商品を1つずつ購入した場合はいくらになるか、合計額をまとめるSQL文を以下に示しました。

SELECT SUBSTRING(商品, -2, 2) 等級, SUM(単価) 合計額 FROM 果物 GROUP BY 等級;

商品名の末尾2文字を切り出し、「等級」列として名づけ合計額を算出するSQL文です。以下の結果が表示されました。

substringを使う場合の注意点

substringは便利ですが、万能ではありません。また活用する際には、注意しておきたいポイントもあります。ここでは注意点を2つ確認していきましょう。

該当する文字列がない場合は空の文字列が返される

データに含まれる文字列の長さは、まちまちの場合が多いです。「果物」表の「商品」列の場合、最短は6文字、最長は10文字です。では以下のSQL文のように9文字目のデータを抽出した場合、どのような結果が返されるのでしょうか。

SELECT 番号, SUBSTRING(商品, 9, 1) FROM 果物;

該当する文字列がない場合は、空の文字列が返されます。結果を確認してください。

3番から6番の4行は商品名が9文字以上のため、該当する文字が表示されます。一方で1番、2番、7番、8番の4行は商品名が8文字以下のため、9文字目を指定した場合は何も表示されません。

日付の取得など、他の関数を使ったほうが便利な場合もある

データの形式によっては、他の関数を使ったほうが便利に使える場合もあります。代表的な例は、日付の取得です。データ「20220222」から年月日「2022年2月22日」を取り出すことはsubstringでも可能ですが、年、月、日のそれぞれについて1回ずつ、計3回使わなければなりません。

日付情報への変換は、関数date_formatを用いることでスムーズに行なえます。substringだけにこだわらず、便利な関数があれば積極的に活用すると良いでしょう。

substringの活用でより良いSQL文を作成できる

substringの活用により、さまざまなデータの加工を実現できます。切り出したデータの表示や変数への格納にとどまらず、加工したデータを検索などの対象列や条件に加えられることも強みです。

より短く効果的なSQL文の作成に、substringは重要な存在です。さまざまな分析にも役立つことでしょう。substringを習得し、より良いSQL文の作成につなげてください。