BigQueryの文字列関数についてまとめる

BigQuery の文字列関数についてまとめる

BigQuery では文字列関数がサポートされています。この記事は BigQuery でよく使う文字列関数を書いていきます。

LENGTH

LENGHTは文字列の長さまたはバイト数を返す関数です。

LENGTH(value)

例

WITH example AS
  (SELECT 'abcあ' AS characters)

SELECT
  characters,
  -- 文字列の長さを返す
  LENGTH(characters) AS string_example,
  -- 文字列のバイト数を返す
  LENGTH(CAST(characters AS BYTES)) AS bytes_example
FROM
  example

+------------+----------------+---------------+
| characters | string_example | bytes_example |
+------------+----------------+---------------+
| 'abcあ'    |              4 |            6  |
+------------+----------------+---------------+

LEFT と RIGHT

LEFTとRIGHTは文字列の先頭または末尾から指定した文字数（またはバイト数）の文字列を返す関数です。

LEFT(value, length)
RIGHT(value, length)

value：valueの型がBYTESの場合、lengthは左端からのバイト数です。valueがSTRINGの場合、lengthは左端からの文字数です。
length：長さを指定する。

例

SELECT
  -- 先頭4文字を抽出する
  LEFT('A00100101', 4) as left_4,
  -- 末尾4文字を抽出する
  RIGHT('A00100101', 4) as RIGHT_4;

+---------+---------+
| left_4  | RIGHT_4 |
+---------+---------+
| A001    | 0101    |
+---------+---------+

CONCAT と SPLIT

CONCATは 1 つ以上の文字また文字列を 1 つの文字列に連結する関数です。また、CONCATと同様な効果を持つ連結演算子||も複数文字列連結することができます。

SPLITは文字列を指定した値で分割する関数です。

CONCAT(value1[, ...])
SPLIT(value[, delimiter])

例

連結

SELECT
  -- CONCATを使って連結する
  CONCAT('田中', '太郎') AS string1,
  -- 連結演算子を使って連結する
  '田中' || '太郎' AS string2

+----------+---------+
| string1  | string2 |
+----------+---------+
| 田中太郎  | 田中太郎|
+----------+---------+

分割

SELECT
  -- SPLITで文字列を' '(スペース)で区切り、offsetで値を取り出す
  -- 0番目の要素helloを取り出す
  SPLIT("hello world", ' ')[offset(0)] AS word1,
  -- 1番目の要素worldを取り出す
  SPLIT("hello world", ' ')[offset(1)]

+----------+---------+
| word1    | word2   |
+----------+---------+
| hello    | word    |
+----------+---------+

SUBSTR

SUBSTRは指定された位置から部分文字列を抽出できる関数です。

SUBSTR(value, position[, length])

position : 部分文字列の開始位置を指定する整数。positionが負の値である場合、valueの最後からカウントする。
length : 返される最大文字数

例

SELECT
  SUBSTR('apple', 2) as example_1,
  SUBSTR('apple', 2, 2) as example_2

+------------+-----------+
| example_1  | example_2 |
+------------+-----------+
|    pple    |     pp    |
+------------+-----------+

CONTAINS_SUBSTR

CONTAINS_SUBSTRは正規化された大文字と小文字を区別せずに検索を実行し、expressionにsearch_value_literalが存在するかどうかを確認することができる関数です。値が存在する場合はTRUEを返し、存在しない場合はFALSEを返します。

CONTAINS_SUBSTR(expression, search_value_literal)

例

SELECT
  CONTAINS_SUBSTR('the blue house', 'Blue house') AS result_true,
  CONTAINS_SUBSTR('the red house', 'blue') AS result_false,


+--------------+--------------+
| result_true  | result_false |
+--------------+--------------+
|   foo@example.com   |            false            |
+--------------+--------------+

REGEXP_CONTAINS と REGEXP_EXTRACT と REGEXP_REPLACE

REGEXP_CONTAINSは上述したCONTAINS_SUBSTRと似たような機能を持っていて、ただし、REGEXP_CONTAINSの場合は正規表現regexpに対してvalueが部分一致である場合、TRUE を返します。

REGEXP_CONTAINS(value, regexp)

REGEXP_EXTRACTは正規表現regexpと一致するvalue内の部分文字列を抽出することができます。

REGEXP_EXTRACT(value, regexp[, position[, occurrence]])

REGEXP_REPLACEは正規表現regexpと一致するvalueのすべての部分文字列をreplacementに置き換えた値を返します。

REGEXP_REPLACE(value, regexp, replacement)

例

REGEXP_CONTAINS

SELECT
  email,
  REGEXP_CONTAINS(email, r'^.+@(example\.com|example\.net)$') AS regexp_contains_example
FROM
  UNNEST(['foo@example.com', 'bar@example.org', 'baz@example.net']) AS email

+---------------------+-----------------------------+
|       email         |   regexp_contains_example   |
+---------------------+-----------------------------+
|   foo@example.com   |            true             |
+---------------------+-----------------------------+
|   bar@example.org   |            false            |
+---------------------+-----------------------------+
|   baz@example.net   |            true             |
+---------------------+-----------------------------+

REGEXP_EXTRACT

SELECT
  email,
  REGEXP_EXTRACT(email, r'^.*@(.*)$') AS mail_domain
FROM
  UNNEST(['foo@example.com', 'bar@example.org', 'baz@example.net']) AS email


+---------------------+-----------------------------+
|       email         |         mail_domain         |
+---------------------+-----------------------------+
|   foo@example.com   |         example.com         |
+---------------------+-----------------------------+
|   bar@example.org   |         example.org         |
+---------------------+-----------------------------+
|   baz@example.net   |         example.net         |
+---------------------+-----------------------------+

REGEXP_REPLACE

SELECT
  REGEXP_REPLACE(heading, r'^# ([a-zA-Z0-9\s]+$)', '<h1>\\1</h1>') AS html
FROM
  UNNEST(['# Heading', '# Another heading']) AS heading

+--------------------------+
|           html           |
+--------------------------+
| <h1>Heading</h1>         |
+--------------------------+
| <h1>Another heading</h1> |
+--------------------------+

補足：正規表現

上記で正則表現について言及しました。 BigQuery は、re2ライブラリを使用した正規表現をサポートしています。正規表現の構文については、該当するドキュメントを確認してください。

参考

備考

Hakky では社内のデータ活用やサービスとしてデータ提供を行うためのソリューションを展開しております。

「BigQuery の管理や使い方に困っている」など具体的な相談はもちろんのこと、「どんなことをお願いできるのか知りたい」や「こんなことをやりたい」など、ご検討段階でも構いませんので、ぜひお気軽にフォームよりお問い合わせくださいませ。