GCPのBigqueryを用いて、不要な文字列のみをマスキング

Question

こんにちは。
GCPのBigqueryにて、下記のような事をしたいのですが、やり方が分からずに困っています。
お知恵をお貸し頂けたらと思います。

＜前提・実現したいこと＞
TBL_AからTBL_Bを1発のSQLで作成したい。
TBL_Bの各カラムは下記の通り。
No：TBL_Aと同じ
name：TBL_Aと同じ
recipe_masking：TBL_Aのrecipe列の材料名のみを残し、「＊」でマスキング

各テーブルのイメージは下記の通りです。
○TBL_A

No	name	recipe
1	野菜炒め	キャベツざく切り、にんじん拍子切り、玉葱薄切りにして豚肉と炒める
2	カレー	牛肉を炒めた後、ジャガイモ、にんじん、玉葱を切り、同じ鍋で炒める

○TBL_B

No	name	recipe_masking
1	野菜炒め	キャベツ＊＊＊＊＊にんじん＊＊＊＊＊玉葱＊＊＊＊＊＊豚肉＊＊＊＊
2	カレー	牛肉＊＊＊＊＊＊ジャガイモ＊にんじん＊玉葱＊＊＊＊＊＊＊＊＊＊＊

＜発生している問題・エラーメッセージ＞
該当なし

＜該当のソースコード＞
該当なし

＜自分で調べたことや試したこと＞
下記のようにやれば、材料部分をマスキング出来るのですが、これの逆をやりたいです。
select
No as No
, name as name
, REPLACE(replace(recipe,'キャベツ','＊＊＊＊'), 'にんじん', '＊＊＊＊) as recipe_masking
from dataset.TBL_A

お忙しい事とは思いますが、ご教示頂けたらと思います。
どうぞ宜しくお願い致します。

Answer 1

REGEXP_REPLACE 1つにまとめるのは私の腕ではできませんでした。
マスク時に文字数は維持するという要件であるならば今回は実現できていません。もしかしたら正規表現では無理かもしれず、その場合は BigQuery の UDF を使って Javascript で変換するのがよいと思います。

with a1 as (
  select 'レシピは、キャベツざく切り、にんじん拍子切り、玉葱薄切りにして豚肉と炒める' as str
), a2 as (
  select REGEXP_REPLACE(str,'(.*?)(キャベツ|にんじん|玉葱|豚肉)(.*?)','***\\2***') as str from a1
), a3 as (
  select REGEXP_REPLACE(str, '^(.*)(キャベツ|にんじん|玉葱|豚肉)(.*?)$','\\1\\2***') as str from a2
)

select * from a3

結果:

***キャベツ******にんじん******玉葱******豚肉***

Comments

[tarou_tabi]

68user様
ご回答、ありがとうございます。
＊の文字数を維持するのは難しいのですね。
実は先程、社内の有識者にも相談したのですが、68user様のご回答と同様、やるならUDFを使うのが良いとのことでした。
仕様変更も含めて、もう一度相談してみます。
ありがとうございました！！

[tarou_tabi]

結局、UDFにて対応することになりました。
ありがとうございました。

Answer 2

結局、SQLで実施は難しいので、UDFにて対応することになりました。
回答下さった68user様、閲覧くださった皆様、ありがとうございました。