【SQL】SUBSTRINGでデータを切り出す！使い方や類似関数を解説

SUBSTRINGは文字列を切り取って返す関数であり、扱えるとデータの抽出や更新が楽になります。本記事では、SUBSTRING関数の基本的な使い方や類似関数である「LEFT関数」「RIGHT関数」を中心に解説します。

SUBSTRINGとは

SQLのSUBSTRINGとは指定した文字列を切り出す関数です。SUBSTRINGには「文字列」という意味があり、その通り文字列操作に使用します。SUBSTRING関数を扱えるとデータの抽出や更新時が楽になるなど、多くの場面で役立ちます。

【基本構文】

SUBSTRING(文字列,開始位置,文字数);

各引数について説明します。

第一引数：文字列

切り出す文字列は数値も扱えます。文字列はシングルクォーテーション（’）で囲いますが、数値には必要ありません。

第二引数：開始位置

切り出しの開始位置を指定します。SQLにおけるSUBSTRINGではほかのプログラムと異なり、先頭位置は「1」になります。また、データベースの種類によっては負の値も指定でき、その場合は末尾の文字から何番目という指定になります。開始位置に0を指定した場合、1を指定した場合と同様の結果になります。しかし、データベースによっては何も返されないか、エラーが発生する可能性もあります。

第三引数：文字数

切り出す文字数を指定します。文字数は省略可能であり、省略すると指定した開始位置から最後まで切り出されます。

1．文字を切り出す

たとえば、「ABCDEF」という文字列があったっとします。以下のコードで2文字目から3文字分を切り出してみます。すると以下のようなコードになります。

【SQL実行コード】

SELECT SUBSTRING('ABCDEF',2,3);

文字列はシングルクォーテーション（’）で囲みます。

【実行結果】

BCD

SUBSTRING関数で切り出した結果をSELECTで抽出・表示しています。二文字目の「B」から3文字分切り取されています。

2．数値を切り出す

今度は「123456789」という数値を切り出します。以下のコードで3文字目から4文字分を切り出してみます。すると以下のようなコードになります。

【SQL実行コード】

SELECT SUBSTRING(123456789,3,4);

数値の場合はシングルクォーテーション（’）で囲む必要はありません。ただし、数字を文字として扱う場合には付けましょう。

【実行結果】

3456

三文字目の「3」から4文字が切り取されています。

2.1．数値で計算も可能

切り出した数値どうしで計算することもできます。

【SQL実行コード】

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

【実行結果】

3579

123+3456で結果は3579になります。

【注意点】データベースによって関数名が異なる

SUBSTRINGに関してはRDBMSやバージョンによって関数名が多少異なる場合があります。

実は、SUBSTRING関数を利用できる主要データベースはSQLServerとMySQLのみです。ただし、ほかの環境で同じ機能を使えないかというとそうではありません。SUBSTRINGと同様の機能を持つ類似関数が用意されています。事前にマニュアルを参照するなどして使用可能な関数名を使用しましょう。

Oracleの場合

Oracleの場合、切り出す長さを文字数単位で指定する場合は「SUBSTR」関数を、バイト数で指定する場合は「SUBSTRB」関数を使用します。

【SQLコード】

SUBSTR(対象,開始位置,文字数);
SUBSTRB(対象,開始位置,バイト数);

対象：カラム名または文字列

PostgreSQLの場合

PostgreSQLの場合、「SUBSTRB」関数と「SUBSTR」関数どちらも使用できます。PostgreSQLでは開始位置に負の値を指定しても、末尾から開始されません。末尾を開始位置にする場合は、LENGTH関数を利用しましょう。

【SQLコード】

SUBSTRING(対象 FROM 開始位置 FOR 文字数);
SUBSTR(対象,開始位置,文字数);

対象：カラム名または文字列

SQL Server

SQL Serverでは開始位置に負の値を指定しても、末尾から開始されません。末尾を開始位置にする場合は、LENGTH関数を利用しましょう。

【SQLコード】

SUBSTRING(対象,開始位置,文字数);

対象：カラム名または文字列

MySQL

MySQLでは開始位置に負の値を指定しても、末尾から開始されません。末尾を開始位置にする場合は、LENGTH関数を利用しましょう。

【SQLコード】

SUBSTRING(対象,開始位置,文字数);
SUBSTRING(対象 FROM 開始位置 FOR 文字数);

対象：カラム名または文字列

このように、関数名や使用方法に多少の違いはありますが、どのデータベースでも同じような機能を利用できます。

SUBSTRINGの使い方

SUBSTRINGの基本作用が分かったところでSUBSTRINGの効果的な使い方をいくつか紹介します。SQL文とそれで得られる結果について具体的に説明します。

1．先頭から文字列を切り出す

先頭から文字列を切り出す場合は、次のコードを実行します。

【SQLコード】

SUBSTRING(文字列,1,文字数)

たとえば、「ABCDEF」という文字列の1文字目から3文字分を切り出すには下記コードになります。

【SQL実行コード】

SELECT SUBSTRING('ABCDEF',1,3);

【実行結果】

ABC

日本語でも数字でも同じように切り出せます。

末尾から文字列を切り出す

末尾から文字列を切り出す場合は、次のコードを実行します。

【SQLコード】

SUBSTRING(文字列,-文字数)

たとえば、「ABCDEF」という文字列の末尾3文字を切り出すには下記コードになります。

【SQL実行コード】

SELECT SUBSTRING('ABCDEF',-3);

【実行結果】

DEF

負の値を指定しているため末尾から切り出されます。

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

先頭から文字列を切り出す場合は、次のコードを実行します。

【SQLコード】

SUBSTRING(文字列,開始位置,文字数)

たとえば、「ABCDEF」という文字列の2文字目から3文字分を切り出すには下記コードになります。

【SQL実行コード】

SELECT SUBSTRING('ABCDEF',2,3);

【実行結果】

BCD

ちなみに、下記の記述でも同様の結果が得られます。

SELECT SUBSTRING('ABCDEF' FROM 2 FOR 3);

基礎的な使い方が分かったところで、ここからは下記の「牛丼（gyuudon）」テーブルを用いてより実践的な使用方法を解説します。

「牛丼（gyuudon）」テーブル

指定した文字列を含むカラムを抽出する

文字列は変数でも問題ありません。牛丼テーブルの末尾が「並盛」で終わる商品を検索します。

【SQL実行コード】

SELECT * FROM gyuudon WHERE SUBSTRING(name,-2,2)='並盛';

「商品名（name）」カラムは変数として使用しているためシングルクォーテーションでは囲みません。

【実行結果】

開始位置に「-2」を指定したことで末尾2文字に「並盛」を含む商品が抽出されます。

このように、SUBSTRINGは前方一致や後方一致といった、指定した文字列を含むデータの抽出に活用できます。なお文字列のどこかに、指定した文字列が含まれるデータの抽出には対応していません。

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

数値の一部を切り出して計算することも可能です。各商品5つ分を購入した時の下二桁の値段を計算しましょう。

【SQL実行コード】

SELECT name,price,SUBSTRING(price,-2,2)*4 FROM gyuudon;

【実行結果】

カレーを3皿買うなら10円玉を持参した方がよさそうですね。

分類別の数値を計算する

SUBSTRINGでは分類ごとの集計も可能です。ただし、分類ごとの文字数が同じである必要があります。各サイズを一種類ずつ購入する場合の合計額を求めます。

【SQL実行コード】

SELECT SUBSTRING(name, -2, 2) AS サイズ,SUM(price) AS 合計額 FROM gyuudon GROUP BY サイズ;

【実行結果】

商品名の末尾2文字を切り出して「サイズ」カラムとし、各サイズの合計額を算出しました。

切り出した文字列でデータを更新する

SELECT文だけでなく、UPDATE文を使ったデータの更新も可能です。価格を9割引きにしてみましょう。

【SQL実行コード】

UPDATE gyuudon SET price=SUBSTRING(price,1,2);

【実行結果】

料金の下一桁を取ったことで9割引きになりました。

このように、テーブル内で計算ができたり、更新できたりと、切り出しタ文字列をダイレクトに処理できるので、わざわざ変数を格納する手間が省けます。

SUBSTRINGを使う場合の注意点

すでにお気づきかもしれませんが、SUBSTRINGは便利なものの万能なわけではありません。活用する上での注意点を紹介します。

【範囲外の指定】該当の文字列がないと空の文字列が返される

「牛丼」「カレー」というように格納されたデータの文字数はまちまちです。たとえば、商品名の5文字目を切り出そうと思っても牛丼や豚丼では空の文字数が返されてしまいます。

このように、該当の文字列が存在しない場合の処理はデータベースによって異なり、範囲外の文字は無視されるか、可能な限りの文字列が返されます。

【SQL実行コード】

SELECT id,SUBSTRING(name,5,1) FROM gyuudon;

このように4文字しかない商品コード「1～6」は切り出せないのです。

日付の取得などには不向き

日付の取得など、取得するデータの形式によってはほかの関数の方が適していることもあります。たとえば、日付の取得では「YYYYMMDD」から年月日を切り出すにはSUBSTRINGでも可能ですが年、月、日でそれぞれ1回ずつ、計3回処理を行わなければなりませんが、「DATE_FORMAT」関数を用いればスムーズに処理を行えます。このようにSUBSTRINGでは複雑の処理になってしまう場合には、ほかの関数も検討しましょう。

パフォーマンスの低下

大抵のクエリでも共通することですが、扱うデータが大きくなったり、データ量が膨大になるほど、特に、多くの行に対してSUBSTRINGを使用するとパフォーマンスに影響が出る可能性があります。

【類似関数】LEFT関数とRIGHT関数の使い方

SUBSTRING関数の類似関数に「LEFT関数」と「RIGHT関数」があります。これらはそれぞれ「左から何文字」「右から何文字」という指定ができ、左右どちらかから切り出す場合に用いられます。

【SQLコード】

LEFT(対象,文字数)
RIGHT(対象,文字数)

これらの関数はSUBSTRING関数とは違って、開始位置を指定する第二引数が存在しません。

SUBSTRINGをうまく活用しよう！

紹介したようにSUBSTRING関数はSQL文で幅広い使い方ができます。このSUBSTRINGをうまく活用することで、より短い分でより効率的な処理が行えます。

SQL関連記事

SQLの勉強方法は？

書籍やインターネットで学習する方法があります。昨今では、YouTubeなどの動画サイトやエンジニアのコミュニティサイトなども充実していて多くの情報が手に入ります。
そして、より効率的に知識・スキルを習得するには、知識をつけながら実際に手を動かしてみるなど、インプットとアウトプットを繰り返していくことが重要です。特に独学の場合は、有識者に質問ができたりフィードバックをもらえるような環境があると、理解度が深まるでしょう。

ただ、SQLに限らず、ITスキルを身につける際、どうしても課題にぶつかってしまうことはありますよね。特に独学だと、わからない部分をプロに質問できる機会を確保しにくく、モチベーションが続きにくいという側面があります。独学でモチベーションを維持する自信がない人にはプログラミングスクールという手もあります。費用は掛かりますが、その分スキルを身につけやすいです。しっかりと知識・スキルを習得して実践に活かしたいという人はプログラミングスクールがおすすめです。

プログラミングスクールならテックマニアがおすすめ！

ITスキル需要の高まりとともにプログラミングスクールも増えました。しかし、どのスクールに通うべきか迷ってしまう人もいるでしょう。そんな方にはテックマニアをおすすめします！これまで多くのITエンジニアを育成・輩出してきたテックマニアでもプログラミングスクールを開講しています。

このような特徴を持つテックマニアはITエンジニアのスタートラインとして最適です。
話を聞きたい・詳しく知りたいという方はこちらからお気軽にお問い合わせください。