【SQL超入門講座】18.SUBSTRING|文字列の一部を切り出す方法

こんにちは、キノコードです。
今回は「substring」について説明します。
substr関数は文字列から一部分を切り出すことができる関数です。

例えばこのような数年分の商品の売り上げデータから、どの月に一番商品が売れるか調べたいとしましょう。
このときsubstring関数を使えば、日付から月の部分だけを切り出すことができるので、集計が楽に行えます。

売上日 売上金額
2021-09-01 ¥12,000
2021-08-23 ¥9,000
2020-09-05 ¥5,000
2020-05-27 ¥10,000

このようにカラムのデータの一部分を切り出して、集計や分類を行うことはよくあります。
そのため、substring関数の使い方はしっかりマスターしておくようにしましょう。

キノコードでは、SQLのレッスンの他に、様々な動画を出しております。
気に入っていただけましたら、新着通知も届きますので、ぜひチャンネル登録をお願いします。

この記事の信頼性

この記事は、Youtubeにて日本最大級のプログラミング教育のチャンネルを運営しているキノコードが執筆、監修しています。
私自身は、2012年からプログラミング学習を始め、2019年以降はプログラミング教育に携わってきた専門家です。
他にも、私には下記のような実績や専門性があります。

  • キノコードは毎月10名以上、合計100名以上ののプログラミング学習者と1対1でお悩みを聞き、アドバイスをしています
  • キノコード自身は、プログラミングスクールに通ったり、本や有料åの動画で勉強してきた経験もあります
  • キノコードは、プログラミング学習サービス「キノクエスト」を運営しています
  • 本の出版、プログラミング雑誌への寄稿の実績があります

プログラミング学習サービス「キノクエスト」のご紹介

キノコードでは、プログラミングを習得するためのプログラミング学習サービス「キノクエスト」を運営しています。
Pythonの問題の他に、SQLの問題も120問以上あります。
キノクエストには、学習カリキュラムがあり、学習順番に悩むことなく学習を進められます。
月額1,990円と本1冊分の値段です。

キノクエストの特徴は下記の通りです。

  • SQLの問題も120問以上
  • プログラミング学習をしている仲間が集まるコミュニティがある
  • 1000問以上の問題を解いてプログラミングを習得
  • 環境構築不要ですぐに始められる
  • 動画と連動しているので、インプットもできる。
  • 月額1,990円で、コミュニティもセット

キノクエストを詳しく知りたい方は、紹介ページをご覧ください。

▼キノクエストの紹介ページはこちら▼
https://kino-code.com/kq_service_a/

それでは、レッスンスタートです。

substringについて

substring関数は対象の文字列から、開始位置と文字数を指定して、一部文を切り出すことができます。
substring関数の基本書式はこちらです。

substring(文字列(カラム名), 開始位置, 文字数)

第一引数に対象の文字列またはカラムを指定します。
第二引数に文字列を切り出す開始位置を整数で指定します。
この開始位置の整数は、プラス、マイナスどちらも取ることができます。
開始位置の整数をNとすると、次のように考えることができます。
もしNがプラスの場合、最初の文字を1番目と考え、最初の文字からN番目が開始位置となります。
例えばNが3であれば、最初の文字から3文字目が開始位置となります。

もしNがマイナスの場合、最後の文字を-1番目と考え、最後の文字からN番目が開始位置となります。
例えばNが-3であれば、最後の文字から3文字前が開始位置となります。

substring関数はDBによって関数名や文法が若干異なります。
substrと記載するものもありますので、使い分けに注意しましょう。

それでは実際にテーブルを用いて、使い方を確認しましょう。
今回使用するテーブルはこちらのtest_tableです。
test_tableはどの社員がいつ、何を売り上げたか記録されているテーブルです。

文字列から切り出して条件抽出

まずtest_tableから、「社員ID」の下二桁が「0~20」のデータを全て抽出してみましょう。
SQLはこのように書くことができます。

SELECT *
FROM test_table
WHERE substring("社員ID",3,2) BETWEEN '00' and '20'

WHERE句にsubstring関数を使って条件を書きます。
第一引数に対象のカラム「社員ID」を指定します。
この社員IDから下二桁を切り出し、条件に合致するか調べます。
社員IDは4桁なので、下二桁の開始位置は最初の文字から数えて3番目です。
そのため第二引数に3と書きます。

もちろん下二桁の開始位置を、最後の文字から数えて2番目と考えてもOKです。
その場合、第二引数には-2と書きましょう。
最後に、第三引数に切り取る文字数の2を書きます。
これで社員IDから下二桁が切り出せたことになります。
このsubstring関数で切り出した社員IDの下二桁が、0から20の間としたいので、between関数を使います。

それではSQLを実行します。

「社員ID」の下二桁が「0~20」のデータが98件抽出されました。

年月ごとの売り上げ集計をする

では次に「売上日の年月ごと」の「売上金額の合計」のデータを抽出してみましょう。
SQLはこのように書くことができます。

SELECT substring("売上日",1,7) 年月,
sum("売上金額") 売上合計
from test_table
group by 年月

売上金額を年月ごとにグループにして集計します。
この売上金額の集計基準となる年月は、売上日からsubstring関数を使って抽出します。

まず第一引数に対象となる売上日のカラムを指定します。
売上日の最初の文字から7番目の文字までが年月の部分です。
そのため第二引数に1と書き、第三引数に7と書きます。
この年月の部分を切り取ったカラムには、そのまま「年月」という別名をつけておきましょう。

年月ごとに売上金額を集計するので、GROUP BY句に年月のカラムを指定します。
そしてSELECT句で年月のカラムを抽出するとともに、SUM関数で売上金額を集計しましょう。
それではSQLを実行します。

「売上金額の合計」が「年月ごと」に12件抽出されました。

今度は「売上日の日ごと」の「売上金額の合計」のデータを抽出してみましょう。
SQLは先ほど作成したものを流用して、このように書くことができます。

SELECT substr(売上日,9) 日, SUM(売上金額) 売上合計
FROM test_table
GROUP BY 日

売上金額の集計が年月ごとではなく、日ごとに変わるだけなので、先ほど作成したSQLと構成は同じです。
先ほどはsubstring関数で売上日から年月を切り出しましたが、日を切り出すよう引数を変更しましょう。

売上日の日の部分の開始位置は、売上日の最初から数えるより、最後から数えたほうが簡単です。
しかし、posgresqlでは、最後から数える概念がありません。
日は売上日の先頭から数えて9番め以降なので、substringの第二引数に9と書きます。 文字数は最後までなので、第三引数は省略できます。
そして日の部分を切り取ったカラムには、そのまま「日」という別名をつけておきましょう。

それではSQLを実行します。

これで、「売上金額の合計」が「日ごと」に31件抽出されました!

ED

さて、substringの説明と使用例をご紹介しましたが、いかがでしたでしょうか?
キノコードでは、これからも色々なSQLのコードを紹介します。
気に入っていただけましたら、ぜひチャンネル登録をお願いします!
それではまた次のレッスンでお会いしましょう!