SQLの文字列抽出には、substring等を使用。メルアドのドメイン名を抽出する方法

SQLで文字列を抽出する方法についてまとめています。

MySQLのサンプルデータベースEmployeesを使用しています。

SQLで文字列を抽出するには、right、left、substring関数を使う

文字列操作関数を使うことで、SQLから文字列の抽出が可能です。

rightは文字列の右端から指定文字数分を抽出、leftは文字列の左端から指定文字数分を抽出、substringは指定文字数の位置から、指定文字数分を抽出する関数です。

mysql> select left('abcdefg', 3);
+--------------------+
| left('abcdefg', 3) |
+--------------------+
| abc                |
+--------------------+
1 row in set (0.00 sec)

mysql> select right('abcdefg', 3);
+---------------------+
| right('abcdefg', 3) |
+---------------------+
| efg                 |
+---------------------+
1 row in set (0.00 sec)

mysql> select substring('abcdefg', 3, 3);
+----------------------------+
| substring('abcdefg', 3, 3) |
+----------------------------+
| cde                        |
+----------------------------+
1 row in set (0.00 sec)

employees（社員テーブル）のhire_date（雇用日）をleft関数で4文字だけ抽出して、hire_year（雇用年）にしてみましょう。以下はemployeesテーブルの内容をそのまま10件抽出するコードです。

mysql> select emp_no, first_name, last_name, hire_date from employees limit 10;
+--------+------------+-----------+------------+
| emp_no | first_name | last_name | hire_date  |
+--------+------------+-----------+------------+
|  10001 | Georgi     | Facello   | 1986-06-26 |
|  10002 | Bezalel    | Simmel    | 1985-11-21 |
|  10003 | Parto      | Bamford   | 1986-08-28 |
|  10004 | Chirstian  | Koblick   | 1986-12-01 |
|  10005 | Kyoichi    | Maliniak  | 1989-09-12 |
|  10006 | Anneke     | Preusig   | 1989-06-02 |
|  10007 | Tzvetan    | Zielinski | 1989-02-10 |
|  10008 | Saniya     | Kalloufi  | 1994-09-15 |
|  10009 | Sumant     | Peac      | 1985-02-18 |
|  10010 | Duangkaew  | Piveteau  | 1989-08-24 |
+--------+------------+-----------+------------+
10 rows in set (0.00 sec)

hire_dateの箇所をleft(hire_date,4)として、左端から4文字抽出するよう修正しました。結果は以下の通りです。

mysql> select emp_no, first_name, last_name, left(hire_date,4) as hire_year from employees limit 10;
+--------+------------+-----------+-----------+
| emp_no | first_name | last_name | hire_year |
+--------+------------+-----------+-----------+
|  10001 | Georgi     | Facello   | 1986      |
|  10002 | Bezalel    | Simmel    | 1985      |
|  10003 | Parto      | Bamford   | 1986      |
|  10004 | Chirstian  | Koblick   | 1986      |
|  10005 | Kyoichi    | Maliniak  | 1989      |
|  10006 | Anneke     | Preusig   | 1989      |
|  10007 | Tzvetan    | Zielinski | 1989      |
|  10008 | Saniya     | Kalloufi  | 1994      |
|  10009 | Sumant     | Peac      | 1985      |
|  10010 | Duangkaew  | Piveteau  | 1989      |
+--------+------------+-----------+-----------+
10 rows in set (0.00 sec)

なお、指定位置から、指定文字数分だけ文字列を切り出す関数substringは、データベースごとに微妙に異なります。

• Oracle:substr()
• MySQL:substring()
• SQL Server:substring()
• PostgreSQL:mid()

指定文字出現以降の文字列を抽出

instrを使って、メールアドレスのドメインのみを抽出するには、以下のように記述します。

select email, substring(email,
  instr(email, '@')+1
 )
from emaillist;

【関連記事】
▶SQLで文字列を切り出すには？MySQLのsubstr関数の使い方 

emaillistデータベースのemailカラムに、user1@domain.comのような形式のデータが入っている前提です。@出現位置の次の位置以降を抽出するため、substring(email, instr(email, ‘@’)+1 )で「domain.com」が抽出されます。

OracleのSubstrbは、バイト数指定で文字列を抽出

Oracleでは、substrのほかに、substrbという関数が用意されています。

書式は、SUBSTRB(文字列, 開始桁,バイト数)で、文字数ではなくバイト数で文字列を抽出します。バイナリデータを扱う際に便利な関数です。

Oracle以外でバイト数指定を実現するには、CONVERT関数でBINARY型に変換してから、substring()関数を使用します。BINARY型の1文字＝1バイトのため、leftやright、substringで文字数指定することで、バイト数指定で文字列を抽出することが可能になります。

ただし、日本語などマルチバイト文字に対して、半端な位置で文字列抽出をおこなうと、文字化けが発生するケースがあります。

mysql> select CAST(LEFT(CAST(name as BINARY), 10) as char(1000)) as name from animal;

参考）【MySQL】文字列をバイト数で切り取る-On Your Mark

まとめ

ポテパンダの一言メモ

• 文字列の抽出は、left、right、substring関数などを使う
• substring関数はデータベースによってはsubstrやmid関数の場合もあり
• 指定文字出現箇所以降の文字列を切り出すには、instrを組み合わせれば可能