SQLにおいて、文字列から特定の部分を抽出する操作は、データ加工時によく使用されます。本記事では、customers テーブルを例に挙げながら、SUBSTRING 関数などを使った文字列の切り出し方法について解説いたします。

1. 使用するサンプルテーブル:customers

以下は、今回使用するサンプルの customers テーブルの内容です。

customer_idcustomer_nameemail
1Tanaka Tarotaro.tanaka@example.com
2Sato Hanakohanako.sato@example.com
3Suzuki Ichiroichiro.suzuki@test.com

2. SUBSTRING関数の基本構文

文字抽出の代表的な関数「SUBSTRING」の、基本的な構文をご紹介いたします。

SUBSTRING(文字列, 開始位置, 文字数)
  • 文字列:対象となる文字列(カラム名も指定可能)
  • 開始位置:1から数えた開始位置
  • 文字数:切り出す文字の長さ

3. 氏名の先頭3文字を抽出する

customer_name カラムの先頭3文字(姓の一部)を抽出する例です。

SELECT
   customer_name,
   SUBSTRING(customer_name, 1, 3) AS name_prefix
FROM customers;

実行結果:

customer_namename_prefix
Tanaka TaroTan
Sato HanakoSat
Suzuki IchiroSuz

4. メールアドレスのドメイン名(@以降)を切り出す

メールアドレスから「@example.com」部分を取り出すには、CHARINDEX 関数と SUBSTRING を組み合わせて使用します(※SQL Serverの場合)。

SELECT
    email,
    SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email)) AS domain
FROM customers;

上記では、対象となる文字列の左から見て最初にある「@」の位置を取得していますので、例えば、1つの文字列内に別の「@」がある場合は注意しましょう。

実行結果:

emaildomain
taro.tanaka@example.comexample.com
hanako.sato@example.comexample.com
ichiro.suzuki@test.comtest.com

5. 名前から名(first name)を切り出す

氏名が「姓+半角スペース+名」という形式で格納されている場合、「名」 部分を抽出するには、空白文字の位置を検索して、そこから後ろを切り出します。

・PostgreSQL / MySQL の場合(INSTR 関数)

SELECT
    customer_name,
    SUBSTRING(customer_name, INSTR(customer_name, ' ') + 1) AS first_name
FROM customers;

・SQL Server の場合(CHARINDEX

SELECT
    customer_name,
    SUBSTRING(customer_name, CHARINDEX(' ', customer_name) + 1, LEN(customer_name)) AS first_name
FROM customers;

実行結果:

customer_namefirst_name
Tanaka TaroTaro
Sato HanakoHanako
Suzuki IchiroIchiro

6. 補足:LEFT および RIGHT 関数の使用例

文字列の左端・右端を切り出すには、LEFTRIGHT 関数も便利です。

SELECT
    email,
    LEFT(email, 5) AS left_part,
    RIGHT(email, 12) AS right_part
FROM customers;

実行結果:

emailleft_partright_part
taro.tanaka@example.comtaro.@example.com
hanako.sato@example.comhanak@example.com
ichiro.suzuki@test.comichiruki@test.com

7. まとめ

本記事では、SUBSTRING を中心とした文字列の切り出し方法について、具体的なテーブルとクエリ例を用いて解説しました。今回は以下がポイントとなります。

  • SUBSTRING は文字列の任意の範囲を取得可能。
  • CHARINDEXINSTR との併用で柔軟な抽出が可能。(検索文字が複数含まれる場合は注意)
  • LEFTRIGHT を使えば簡潔に先頭・末尾を取得可能。