背景

BigQuery で文字列の中にあるスペースを REGEXP_REPLACE を使って置換したところ、置換できない半角スペースがあることを発見！

例えば、以下のsampleテーブルがあったとして、

sample テーブル

id	str
001	AAA BBB CCC
002	A A B B C C
003	12 345 678

この str の半角スペース、全角スペースを REGEXP_REPLACE(str, r' |　', '+') にて「＋」に置換して以下の結果を期待しています。

sample テーブル

id	str	置換後の期待する値
001	AAA BBB CCC	AAA+BBB+CCC
002	A A B B C C	A+A+B+B+C+C
003	12 345 678	12+345+678

しかし、実際には以下のような結果でした。

sample テーブル

id	str	実際の置換後の値
001	AAA BBB CCC	AAA BBB+CCC
002	A A B B C C	A A B+B+C+C
003	12 345 678	12 345 678

上記のsampleテーブル作成用裏クエリ

SELECT
  "001" AS id
  , CONCAT(REPLACE("AAA BBB", chr(32), chr(160))
      , " CCC"
    ) AS str

UNION ALL

SELECT
  "002" AS id
  , CONCAT(REPLACE("A A B", chr(32), chr(160))
      , " B B C C"
    ) AS str

UNION ALL

SELECT
  "003" AS id
  , CONCAT(REPLACE("12 345", chr(32), chr(160))
      , " 678"
    ) AS str

置換できない半角スペースがあるが、これは何だ？となり、それを調査するところから、解決するまでを少々手こずったので残しておきます。

置換できない半角スペースの調査

以下のクエリで各文字の Unicodeコードを調べました。

SELECT
  id
  , str
  , FORMAT("%T", ARRAY_CONCAT_AGG(TO_CODE_POINTS(str))) as str_code
FROM
  sample
GROUP BY
  id
  , str

結果

id	str	str_code
001	AAA BBB CCC	[65, 65, 65, 160, 66, 66, 66, 32, 67, 67, 67]
002	A A B B C C	[65, 160, 65, 160, 66, 32, 66, 32, 67, 32, 67]
003	12 345 678	[49, 50, 160, 51, 52, 53, 32, 54, 55, 56]

普通の半角スペースは「32」(0x20) ですが、REGEXP_REPLACE で置換できなかった半角スペースは「160」(0xA0) になっていました。

このコードを調べたところ、ノーブレークスペースというものだと判明。

ノーブレークスペースの置換方法

このノーブレークスペースを置換する方法を調べたところ、こちらの記事がとても参考になりました。

以下のクエリにてノーブレークスペースと普通の半角スペースのどちらも置換できました。

SELECT
  *
  , REGEXP_REPLACE(str, '[\u00A0\\s]|　', '+') AS str_replace
FROM
  sample

`[\u00A0\\s]` とは？

\u00A0
- \u は unicode
- 00A0 はノーブレークスペースの unicode
\\s
- 空白文字 [ \t\r\n\f\v] ← （半角スペース、タブ、改行、改ページ）