BigQuery での改行を含む文字列と値の埋め込み

BigQuery で改行を含む文字列を使用する方法と、文字列に値を埋め込む方法のメモ。

改行を含む文字列

文字列を ''' でくくると改行を含む文字列を使用することができます。

select '''
abc
def
ghi
''';

実行結果

abc
def
ghi

文字列への値の埋め込み

format() を使用して文字列に値を埋め込むことができます。

select format('''
string: "%s"
integer: "%d"
float: "%f"
''', 'abc', 123, 123.456);

実行結果

string: "abc"
integer: "123"
float: "123.456000"

BigQuery での文字列の正規化

BigQuery での文字列を正規化する方法のメモ。

ここでは以下の文字列の正規化を行います。

• 全角英数記号⇒半角、半角カナ⇒全角など
• 英大文字⇒子文字
• 制御文字（改行、タブ等）を半角空白に
• 連続する空白文字を1文字に
• 先頭、末尾の空白文字の削除

全角英数記号⇒半角、半角カナ⇒全角など

normalize(string, "NFKC") で正規化を行います。

実行例

select normalize("ＡＢＣ０１２！＃＄ｶﾅ", NFKC);
ABC012!#$カナ

英大文字⇒小文字

lower(string) で英大文字を小文字に変換します。

実行例

select lower("ＡＢＣABC");
ａｂｃabc

制御文字（改行、タブ等）を半角空白に

正規表現で制御文字を半角空白に置換します。

select regexp_replace('a\n\r\tb', r'[\x00-\x1f\x7f]', ' ');
a   b

to_code_points(string) で各文字をコードポイントに変換し、 code_points_to_bytes(code_points) でさらにバイトに変換し、 to_hex(bytes) で16進数に変換してみます。

select
  to_hex(code_points_to_bytes(to_code_points(
    regexp_replace('a\n\r\tb', r'[\x00-\x19\x7f]', ' ')
  )));
6120202062

61:'a'、20:' '、62:'b' のため、改行やタブを半角空白に変換できていることがわかります。

連続する空白文字を1文字に

正規表現で連続する空白文字を1文字に変換します。

select regexp_replace('a b  c   d    e', r'[ ]{2,}', ' ');
a b c d e

先頭、末尾の空白文字の削除

先頭、末尾の不要な空白文字列を正規表現で削除します。

select regexp_replace('  a b c  ', r'(?:^[ ]+|[ ]+$)', '');
a b c

上記をまとめた正規化関数

create or replace function
  dataset.normalize_string(str string)
as (
  regexp_replace(
    regexp_replace(
      regexp_replace(
        lower(
          normalize(str, NFKC)
        )
        , r'[\x00-\x1f\x7f]', ' '
      )
      , r'[ ]{2,}', ' '
    )
    , r'(?:^[ ]+|[ ]+$)', ''
  )
);

select dataset.normalize_string(' ＡＢＣ  ｶﾅ　　＃！  ');
abc カナ #!