Bigqueryで様々な方法で文字列を取り出す

(“birth=19000401&name=hige&age=28″,”1999-01-31″,”1999-01-31”)

SPLITで特定の文字列の何番目かを抽出する方法についてやってみます。
よくある特定の文字で区切られた文字列から何番目かを抜出す時にSPLITは最適です。

DEMO1の文字列の”&”で区切られている1番目のb=xxxxxxxxの部分のみ切出ししてみる。

関数で書いてみる場合はこのようになります。

SELECT
DEMO1,
SPLIT(DEMO1,”&”)[SAFE_OFFSET(0)]
FROM
`BQを使ってるプロジェクト名.データセット名.作成したテーブル名`

SPLIT(抜出しを実行するカラムを指定,”分割したい文字列を指定”)[SAFE_OFFSET(0)]

実際に書いてみると”birth=xxxxxxxx”の部分だけ抜き出すことができました。
[SAFE_OFFSET(0)]のところを「1」にしてしまうとどうなるかというと”name=xxxx”が抜出されます。

指定したカラム内の文字列に指定した文字列が存在しない場合はそのままのそのままの値が返ってきます。

※[SAFE_OFFSET(n)]が無いと配列の形になってしまうので必ず入れましょう。

【REGEXP_EXTRACT】

REGEXP_EXTRACT関数で指定の文字列の後の文字列を抜き出ししてみます。
REGEXPというのは正規表現になります。

今回はnameの値のみ抜出したいので下記のように関数を書いてみます。

SELECT
DEMO1,
REGEXP_EXTRACT(DEMO1,r”name=([a-z]{1,10})”),
–または
REGEXP_EXTRACT(DEMO1,r”name=([\w]{1,10})”)
FROM
`BQを使ってるプロジェクト名.データセット名.作成したテーブル名`

正規表現にマッチした文字列の後の文字列を抜出す事が可能な関数になります。

REGEXP_EXTRACTでは()で囲われた箇所の値を抜出しすることが可能です。
この正規表現での抜出しですが、先ほど学んだSPLITの代用にもなりますので確認しておきましょう。

同じように”birth=xxxxxxxx”の箇所を抜出しする場合

SELECT
REGEXP_EXTRACT(DEMO1,r”([^&]*)”)
FROM
`BQを使ってるプロジェクト名.データセット名.作成したテーブル名`

このような形で抜出しすることが可能です。
また”name=xxxx”の部分を抜出しする場合は

SELECT
REGEXP_EXTRACT(DEMO1,r”[^&]*&([^&]*)”)
FROM
`BQを使ってるプロジェクト名.データセット名.作成したテーブル名`

このような形で抜出しの代用をすることも可能です。
正規表現での文字列の抜出しは汎用性が高く便利なので是非活用してみてください。