BigQueryのconcatで文字列が結合されない原因

2021-01-14

BigQueryのconcatで文字列が結合されない。

文字列結合で便利な関数 concat()。

引数が２つに限らず任意の個数の文字列を結合できて便利な関数だが、結果がうまく結合されずに NULL になってしまうことがある。

一体何故文字列が結合されずに NULL になってしまうのか。

そこで今回はBigQueryのconcatで文字列が結合されない原因について解説する。

この記事を書いている人

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

記事を読むメリット

BigQueryのconcatで文字列が結合されない原因がわかる

BigQueryのconcatで文字列が結合されない原因。

それは結合する対象にNULLを含んでいるから。

では確認の為に実験してみる。

まず結合する要素に NULLを含めずに concat() してみる。

SELECT
txt1,txt2,txt3,
concat(txt1,txt2,txt3) as concat_txt
FROM
(
  SELECT
  "a" as txt1,
  "b" as txt2,
  "c" as txt3
)

結果

Row	txt1	txt2	txt3	concat_txt
1	a	b	c	abc

次に結合する要素に NULL を含めて concat() してみる。

SELECT
txt1,txt2,txt3,
concat(txt1,txt2,txt3) as concat_txt
FROM
(
  SELECT
  "a" as txt1,
  NULL as txt2,
  "c" as txt3
)

結果

Row	txt1	txt2	txt3	concat_txt
1	a	null	c	null

確かに結合する要素に NULL を含めると concat() の結果が NULL になった。

回避方法

concat() の要素にNULLを含んでも他の要素だけで文字列結合したい場合は、 IFNULL() でNULLをあらかじめ空白などに置換しておく。

SELECT
txt1,txt2,txt3,
concat(txt1,IFNULL(txt2,""),txt3) as concat_txt
FROM
(
  SELECT
  "a" as txt1,
  CAST(NULL as string) as txt2,
  "c" as txt3
)

CASTしているのは元のカラムの型をStringに指定する為。

既存のテーブルでカラムの型をString指定していればCASTしなくても問題ないが、指定しないと concat() で以下のエラーになる。

No matching signature for function IFNULL for argument types:
STRING, INT64. Supported signature: IFNULL(ANY, ANY)

結果

Row	txt1	txt2	txt3	concat_txt
1	a	null	c	ac

参考

今回はBigQueryのconcatで文字列が結合されない原因について解説した。

concatで文字列が結合されない原因は、結合する要素にNULLを含んでいるから。

回避方法としてはNULLが入りそうなカラムに IFNULL() を適用して事前にNULLを空白などに置換しておくこと。

リンク

他にもSQLのちょっとしたテクニックについてまとめているので、もし気になったら見てみて欲しい。