カテゴリ：

文字列とバイナリ関数 （マッチング/比較）

SUBSTR , SUBSTRING

start_expr で指定された文字/バイトから開始し、オプションで長さを制限して、 base_expr から 文字列またはバイナリ 値の一部を返します。

これらの関数は同義語です。

こちらもご覧ください:

LEFT 、 RIGHT

構文

SUBSTR( <base_expr>, <start_expr> [ , <length_expr> ] )

SUBSTRING( <base_expr>, <start_expr> [ , <length_expr> ] )

引数

base_expr

VARCHAR または BINARY 値に評価される式。

start_expr

整数に評価される式。部分文字列が始まるオフセットを指定します。オフセットは次の単位で測定されます。

  • 入力が VARCHAR の場合、 UTF-8文字の数。

  • 入力が BINARY の場合のバイト数。

開始位置は0ベースではなく、1ベースです。例えば、 SUBSTR('abc', 1, 1) は、 b ではなく、 a を返します。

length_expr

整数に評価される式。次を指定します。

  • 入力が VARCHAR の場合に返す UTF-8文字の数。

  • 入力が BINARY の場合に返すバイト数。

0以上の長さを指定します。長さが負の数の場合、関数は空の文字列を返します。

戻り値

返される値のデータ型は、 base_expr （VARCHAR または BINARY）のデータ型と同じです。

入力のいずれかが NULLの場合、 NULLが返されます。

使用上の注意

  • length_expr が指定された場合、 length_expr 文字/バイトまでが返されます。 length_expr が指定されていない場合は、文字列またはバイナリ値の最後までのすべての文字が返されます。

  • start_expr の値は1から始まります。

    • 0が指定されている場合、1として扱われます。

    • 負の値が指定されている場合、開始位置は、文字列またはバイナリ値の末尾から start_expr 文字/バイトとして計算されます。位置が文字列またはバイナリ値の範囲外の場合、空の値が返されます。

照合順序の詳細

  • 照合は VARCHAR 入力に適用されます。最初のパラメーターの入力データ型が BINARY の場合、照合は適用されません。

  • No impact. 照合順序は構文的に受け入れられますが、照合順序は処理には影響しません。例えば、2文字や3文字の文字（ハンガリー語の「dzs」やチェコ語の「ch」など）は、長さの引数では（1文字ではなく）2文字や3文字としてカウントされます。

  • The collation of the result is the same as the collation of the input. これは、戻り値がネストされた関数呼び出しの一部として別の関数に渡される場合に役立ちます。

例

以下の例では、 SUBSTR 関数を使用しています。

基本的な例

次の例では、 SUBSTR 関数を使って、文字列の9文字目から始まる部分を返し、返される値の長さを3文字に制限しています。

SELECT SUBSTR('testing 1 2 3', 9, 3);

+-------------------------------+
| SUBSTR('TESTING 1 2 3', 9, 3) |
|-------------------------------|
| 1 2                           |
+-------------------------------+

異なる開始と長さの値の指定

次の例は、 start_expr と length_expr に異なる値を指定した場合に、同じ base_expr に対して返される部分文字列を示しています。

CREATE OR REPLACE TABLE test_substr (
    base_value VARCHAR,
    start_value INT,
    length_value INT)
  AS SELECT
    column1,
    column2,
    column3
  FROM
    VALUES
      ('mystring', -1, 3),
      ('mystring', -3, 3),
      ('mystring', -3, 7),
      ('mystring', -5, 3),
      ('mystring', -7, 3),
      ('mystring', 0, 3),
      ('mystring', 0, 7),
      ('mystring', 1, 3),
      ('mystring', 1, 7),
      ('mystring', 3, 3),
      ('mystring', 3, 7),
      ('mystring', 5, 3),
      ('mystring', 5, 7),
      ('mystring', 7, 3),
      ('mystring', NULL, 3),
      ('mystring', 3, NULL);

SELECT base_value,
       start_value,
       length_value,
       SUBSTR(base_value, start_value, length_value) AS substring
  FROM test_substr;

+------------+-------------+--------------+-----------+
| BASE_VALUE | START_VALUE | LENGTH_VALUE | SUBSTRING |
|------------+-------------+--------------+-----------|
| mystring   |          -1 |            3 | g         |
| mystring   |          -3 |            3 | ing       |
| mystring   |          -3 |            7 | ing       |
| mystring   |          -5 |            3 | tri       |
| mystring   |          -7 |            3 | yst       |
| mystring   |           0 |            3 | mys       |
| mystring   |           0 |            7 | mystrin   |
| mystring   |           1 |            3 | mys       |
| mystring   |           1 |            7 | mystrin   |
| mystring   |           3 |            3 | str       |
| mystring   |           3 |            7 | string    |
| mystring   |           5 |            3 | rin       |
| mystring   |           5 |            7 | ring      |
| mystring   |           7 |            3 | ng        |
| mystring   |        NULL |            3 | NULL      |
| mystring   |           3 |         NULL | NULL      |
+------------+-------------+--------------+-----------+

メール、電話、日付文字列の部分文字列を返す

以下の例は、テーブル内の顧客情報の部分文字列を返します。

テーブルを作成してデータを挿入します。

CREATE OR REPLACE TABLE customer_contact_example (
    cust_id INT,
    cust_email VARCHAR,
    cust_phone VARCHAR,
    activation_date VARCHAR)
  AS SELECT
    column1,
    column2,
    column3,
    column4
  FROM
    VALUES
      (1, 'some_text@example.com', '800-555-0100', '20210320'),
      (2, 'some_other_text@example.org', '800-555-0101', '20240509'),
      (3, 'some_different_text@example.net', '800-555-0102', '20191017');

SELECT * from customer_contact_example;

+---------+---------------------------------+--------------+-----------------+
| CUST_ID | CUST_EMAIL                      | CUST_PHONE   | ACTIVATION_DATE |
|---------+---------------------------------+--------------+-----------------|
|       1 | some_text@example.com           | 800-555-0100 | 20210320        |
|       2 | some_other_text@example.org     | 800-555-0101 | 20240509        |
|       3 | some_different_text@example.net | 800-555-0102 | 20191017        |
+---------+---------------------------------+--------------+-----------------+

メールアドレスからドメインを抽出するには、 POSITION 関数と SUBSTR 関数を使用します。この例では、各文字列の @ の位置を求め、1文字追加して次の文字から開始します。

SELECT cust_id,
       cust_email,
       SUBSTR(cust_email, POSITION('@' IN cust_email) + 1) AS domain
  FROM customer_contact_example;

+---------+---------------------------------+-------------+
| CUST_ID | CUST_EMAIL                      | DOMAIN      |
|---------+---------------------------------+-------------|
|       1 | some_text@example.com           | example.com |
|       2 | some_other_text@example.org     | example.org |
|       3 | some_different_text@example.net | example.net |
+---------+---------------------------------+-------------+

Tip

POSITION 関数を使用すると、空文字（' '）やアンダースコア（_）など、他の文字の位置を見つけることができます。

テーブルの cust_phone 列では、市外局番は常に最初の3文字です。電話番号から市外局番を抽出します。

SELECT cust_id,
       cust_phone,
       SUBSTR(cust_phone, 1, 3) AS area_code
  FROM customer_contact_example;

+---------+--------------+-----------+
| CUST_ID | CUST_PHONE   | AREA_CODE |
|---------+--------------+-----------|
|       1 | 800-555-0100 | 800       |
|       2 | 800-555-0101 | 800       |
|       3 | 800-555-0102 | 800       |
+---------+--------------+-----------+

電話番号から市外局番を削除します。

SELECT cust_id,
       cust_phone,
       SUBSTR(cust_phone, 5) AS phone_without_area_code
  FROM customer_contact_example;

+---------+--------------+-------------------------+
| CUST_ID | CUST_PHONE   | PHONE_WITHOUT_AREA_CODE |
|---------+--------------+-------------------------|
|       1 | 800-555-0100 | 555-0100                |
|       2 | 800-555-0101 | 555-0101                |
|       3 | 800-555-0102 | 555-0102                |
+---------+--------------+-------------------------+

テーブルの activation_date 列では、日付は常に YYYYMMDD の形式です。これらの文字列から年、月、日を抽出します。

SELECT cust_id,
       activation_date,
       SUBSTR(activation_date, 1, 4) AS year,
       SUBSTR(activation_date, 5, 2) AS month,
       SUBSTR(activation_date, 7, 2) AS day
  FROM customer_contact_example;

+---------+-----------------+------+-------+-----+
| CUST_ID | ACTIVATION_DATE | YEAR | MONTH | DAY |
|---------+-----------------+------+-------+-----|
|       1 | 20210320        | 2021 | 03    | 20  |
|       2 | 20240509        | 2024 | 05    | 09  |
|       3 | 20191017        | 2019 | 10    | 17  |
+---------+-----------------+------+-------+-----+