BigQueryで全角半角変換など文字列の正規化する方法

半角文字や全角文字が混在しているテキストを正規化して揃えたい。

テキストデータを扱っていると、全角の ＥＧＧ と半角の EGG のような全角と半角だけど同じ文字で書かれたテキストを同一として扱いたくなる。

しかし全角文字と半角文字はデータ上は別物なので、比較演算子で比較しても同じものとしては扱われない。

また大文字や小文字のように upper(), lower() で変換するわけにも行かない。

ではどうやって全角半角を統一すればいいのか。

そこで今回はBigQueryで全角半角変換など文字列の正規化する方法について解説する。

この記事を書いている人

システムエンジニア、AIエンジニアと、IT業界で10年以上働いている中堅。PythonとSQLが得意。最近GCPを色々と習得中。

データの準備

まずはデータを準備する。

CREATE OR REPLACE TABLE
pj_name.test.tmp_text
AS
(
  (select "ＥＧＧ" as text)
  union all
  (select "EGG" as text)
  union all
  (select "エッグ" as text)
  union all
  (select "ｴｯｸﾞ" as text)
) 

作成されたテーブルを確認する。

select * from pj_name.test.tmp_text

結果

冒頭で述べたように全角テキストと半角テキストは別のデータなので、 distinct で重複を省いても全角と半角は重複扱いにはならないので、データ数は減らない。

select
distinct text
from pj_name.test.tmp_text

結果

BigQueryで全角半角変換など文字列の正規化する方法

ではどのように全角半角変換するのかというと、normalize() 関数を用いる。

BigQueryには文字列の正規化用に normalize() 関数が存在する。

NORMALIZE(value[, normalization_mode])
説明

文字列値を取り、正規化された文字列として返します。

正規化は、2 つの文字列を同等にするために使用されます。画面上で同じように表示される 2 つの文字列が、実際には異なる Unicode コードポイントを持つような状況で、正規化がよく使用されます。

参考：標準 SQL の式、関数、演算子  |  BigQuery  |  Google Cloud

正規化のモードは4種類あるが、半角全角の正規化をするなら NFKC が良いだろう。

実際に使ってみると以下のようになる。

NFKC正規化によって半角カタカナは全角になり、全角英数は半角英数に変換される。

select
normalize(text, NFKC) as n_text
from pj_name.test.tmp_text

結果

そして全角半角の片方に正規化されたテキストはデータとしても一致するので、distinct による重複の省略対象にできる。

select
distinct
normalize(text, NFKC) as n_text
from pj_name.test.tmp_text

結果

まとめ

今回はBigQueryで全角半角変換など文字列の正規化する方法について紹介した。

正規化するには normalize() 関数を用いる。

簡単だね！

2017年の時点ではこの関数は無かったようで、自作の関数で対応するしかなかったみたいですね

便利になったんやね

他にもBigQuery関連の記事もあるので、もし気になったら見ていって欲しい。