SQLにおいて、文字列から特定の部分を抽出する操作は、データ加工時によく使用されます。本記事では、customers
テーブルを例に挙げながら、SUBSTRING
関数などを使った文字列の切り出し方法について解説いたします。
Table of Contents
1. 使用するサンプルテーブル:customers
以下は、今回使用するサンプルの customers
テーブルの内容です。
customer_id | customer_name | |
---|---|---|
1 | Tanaka Taro | taro.tanaka@example.com |
2 | Sato Hanako | hanako.sato@example.com |
3 | Suzuki Ichiro | ichiro.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_name | name_prefix |
---|---|
Tanaka Taro | Tan |
Sato Hanako | Sat |
Suzuki Ichiro | Suz |
4. メールアドレスのドメイン名(@以降)を切り出す
メールアドレスから「@example.com」部分を取り出すには、CHARINDEX
関数と SUBSTRING
を組み合わせて使用します(※SQL Serverの場合)。
SELECT
email,
SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email)) AS domain
FROM customers;
上記では、対象となる文字列の左から見て最初にある「@」の位置を取得していますので、例えば、1つの文字列内に別の「@」がある場合は注意しましょう。
実行結果:
domain | |
---|---|
taro.tanaka@example.com | example.com |
hanako.sato@example.com | example.com |
ichiro.suzuki@test.com | test.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_name | first_name |
---|---|
Tanaka Taro | Taro |
Sato Hanako | Hanako |
Suzuki Ichiro | Ichiro |
6. 補足:LEFT
および RIGHT
関数の使用例
文字列の左端・右端を切り出すには、LEFT
や RIGHT
関数も便利です。
SELECT
email,
LEFT(email, 5) AS left_part,
RIGHT(email, 12) AS right_part
FROM customers;
実行結果:
left_part | right_part | |
---|---|---|
taro.tanaka@example.com | taro. | @example.com |
hanako.sato@example.com | hanak | @example.com |
ichiro.suzuki@test.com | ichir | uki@test.com |
7. まとめ
本記事では、SUBSTRING
を中心とした文字列の切り出し方法について、具体的なテーブルとクエリ例を用いて解説しました。今回は以下がポイントとなります。
SUBSTRING
は文字列の任意の範囲を取得可能。CHARINDEX
やINSTR
との併用で柔軟な抽出が可能。(検索文字が複数含まれる場合は注意)LEFT
、RIGHT
を使えば簡潔に先頭・末尾を取得可能。