SQL substring関数の使い方 整数・小数も切り出し&加算可能
  • facebookページ
  • twitterページ
  • 2019.10.20

    SQL substring関数の使い方 整数・小数も切り出し&加算可能

    SQLで文字列操作をおこなう関数substringのサンプルコードを紹介しています。

    以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。

    SQLのsubstring関数の基本的な使い方


    基本の構文は、「substring(カラム名または値,開始位置、終了位置)」です。

    DBMSによって、文法が異なります。以下は、MySQLで動作するサンプルコードです。

    上記SQLは、employees(社員テーブル)から、first_name(姓名の名)の1文字目とlast_name(姓名の名)の1文字目をsubstring関数で抽出し、concat関数で連結してイニシャルを取得しています。

    実行するとこうなります。

    SQL実行結果

    DBMSごとのsubstringの違い


    substring関数はデータベースによって微妙に文法、引数などが異なります。

    Oracleではsubstr

    関数名がsubstrになります。

    構文は、「substr(string, position [, length])」。

    参考)Oracle公式ドキュメント SUBSTR

    文字列の左端、右端を取得するleft関数、right関数がないためsubstr関数で代替します。

    それぞれ以下のように記述します。

    • left(x,3)の代替  substr(x,1,3)
    • right(x,3) の代替 substr(x,-3)

    開始位置にマイナスを指定することで、文字列の右端からx文字目までを抽出する動きになります。

    また、Oracleではバイト数指定で文字列やバイナリを抽出できるsubstrbという専用関数が用意されています。

    SQL Serverは、文字列操作関数が充実

    SQL Serverでのsubstring構文は、「SUBSTRING ( expression ,start , length )」

    数値、文字列、イメージ型を扱うことができます。

    参考)SUBSTRING (Transact-SQL) – SQL Server | Microsoft Docs

    他のDBMSと比べると、left関数、right関数に加えてLTRIM(左端の空白を除去)、RTRIM(右端の空白を除去)、STRING_SPLIT(指定文字で文字列を分割)が用意されているなど、文字列操作関数が充実しています。

    PostgreSQLは、正規表現が使える

    postgreSQLでのsubstring構文はカンマを使わない点に注意。

    「substring(string from 開始位置 for 文字の長さ)」となります。

    他のDBMSと比較すると正規表現が使える点が特徴です。POSIX正規表現と、SQL正規表現が仕様可能。

    POSIX正規表現の場合は「substring(string from 正規表現のパターン)」、SQLの正規表現の場合は「substring(string from 正規表現パターン for エスケープ文字)」となります。

    参考)PostgreSQL 11のマニュアル 9.4. 文字列関数と演算子

    MySQL 他のDBMS表記をそのまま使える

    MySQLのsubstringは、他のDBMSの指定方法を流用できるようにいくつかのシノニムが用意されています。

    • SUBSTR(str,pos)
    •  SUBSTR(str FROM pos)
    •  SUBSTR(str,pos,len)
    •  SUBSTR(str FROM pos FOR len)

    これにより、OracleやPostgreSQLの表記で、そのまま動作します。

    参考)MySQL :: MySQL 8.0 Reference Manual :: 12.5 String Functions and Operators

    また、MySQLにはsubstring_indexという関数が用意されていて、csv形式(カンマ区切り)やウェブのドメイン名指定(ドット区切り)、ファイルのパス指定(スラッシュや円マーク区切り)の文字列を指定番目の区切り文字で抽出することが容易になっています。

    substringの注意点

    日付をあらわす文字列から年月日を取得するには、substringよりdate_format

    日付をあらわす文字列から、substringを使って年月日を抽出できます。

    ※hire_dateの形式は「YYYY-MM-DD」

    上記SQLは、employees(社員テーブル)のhire_date(雇用日)の1~4文字目を年、6~7文字目を月、9~10文字目を日としてconcat関数で連結して取得しています。

    実行するとこうなります。

    SQL実行結果

    場合によっては、date_formatなどの日付フォーマット変換関数を使った方が簡単に記述できる場合もあります。

    整数にもsubstringが使える

    substring関数は、整数や小数にも適用可能です。※MySQLの場合

    またsubstring関数で抽出した数値は、足し算などの演算が可能です。

    上記SQLは、「12345」の2~4文字目(23)、「3.14159」の3~4文字目(14)、「1234567」の3~4文字目(34)+「7654321」の2~4文字目(654)=688を表示します。

    実行するとこうなります。

    where条件にsubstringを使うと、レスポンスが悪くなる可能性あり

    以下のSQLのようにwhere条件に関数を使うと、substringに限らずレスポンスが悪くなる可能性があります。設定されたインデックスが使用できず、テーブルフルスキャンが実行されるためです。

    ※salaries(年収テーブル)のデータから、emp_no(社員番号)の先頭2桁が20、salary(年収)の上2桁が60のデータを取得します。

    大量データの入ったテーブルにsubstringを使ったwhere条件を適用する場合は、別の絞り込み条件で対象処理件数を減らせないかどうか検討するようにしましょう。

    まとめ

    ポテパンダの一言メモ
    • substringの構文は、DBMSごとに微妙に異なる
    • 日付文字列を扱うにはsubstringよりも日付フォ―マット関数のほうが簡単なケースあり
    • 整数、小数に対してもsubstringが使えるケースあり
    • where条件にsubstrを使うとレスポンス悪化の可能性あり

    ポテパンが提供するサービスについて

    本メディア「ポテパンスタイル」を運営する株式会社ポテパンは、エンジニアキャリア領域で複数サービスを提供しています。

    ポテパンフリーランス

    ポテパンフリーランス

    フリーランスエンジニアの方に高単価案件をご紹介しております。弊社ではフリーランス案件を常時300件ほど保有しており、その中からあなたに適した案件をご案内いたします。また、これから独立してフリーランスになる方の無料個別相談も承っております。フリーランスになった後の案件獲得方法やお金面(税金や保険など)についてお答えいたします!フリーエンジニアになりたい方向けのコンテンツも盛りだくさんです。

    ポテパンキャリア

    ポテパンキャリア

    エンジニア職専門の転職エージェントです。ポテパンキャリアでは、技術のわかるエージェントがあなたの転職をサポートします。エージェント自身がエンジニアなので、あなたと同じ目線で仕事内容や今後のキャリアについて一緒に考えることができます。年収800万円以上のハイスペック転職をご希望の方は「ポテパンプロフェッショナル」もご用意しておりますのでご利用下さいませ。

    ポテパンキャンプ

    ポテパンキャンプ

    ポテパンキャンプでは、RubyにてゼロからオリジナルのECサイトを作り上げてる3ヶ月間の実践型カリキュラムを提供しております。すでに本スクールの卒業生は、エンジニア職として様々な企業様に就職しております。なお、本スクールは受講料25万円と他社スクールに比べ格安となっており、またポテパンからご紹介させていただいた企業へ就職が決まった場合は、全額キャッシュバックいたします。



    株式会社ポテパンは、企業とエンジニアの最適なマッチングを追求しています。気になるサービスがあれば、ぜひ覗いてみてください!

    ポテクラバナー ポテプロバナー

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE

    ポテパンおすすめ案件