如何在BigQuery中拆分字符串，移除另一列的初始子串？

原始数据SQL #

WITH

foo as (
  SELECT "Field Outfield" AS Zone, "Field Outfield 109" AS Section
  UNION ALL SELECT "Bleachers" AS Zone, "Bleachers 202" AS Section
  UNION ALL SELECT "Delta Suites" AS Zone, "Delta Suite 218 A" AS Section
  UNION ALL SELECT "Jim Beam Suites" AS Zone, "Terrace 317" AS Section
)

SELECT * FROM foo

原始查询结果 #

目标结果 #

解决方案（向量化实现） #

可以直接用REGEXP_REPLACE()结合列值动态生成正则表达式，实现全表的向量化处理，无需逐行判断。核心思路是：针对每一行，用Zone的值作为匹配前缀，替换掉Section开头的该前缀及后续空格，最后清理多余空格。

完整SQL如下：

WITH

foo as (
  SELECT "Field Outfield" AS Zone, "Field Outfield 109" AS Section
  UNION ALL SELECT "Bleachers" AS Zone, "Bleachers 202" AS Section
  UNION ALL SELECT "Delta Suites" AS Zone, "Delta Suite 218 A" AS Section
  UNION ALL SELECT "Jim Beam Suites" AS Zone, "Terrace 317" AS Section
)

SELECT
  Zone,
  TRIM(REGEXP_REPLACE(Section, CONCAT('^', REGEXP_ESCAPE(Zone), '\\s*'), '')) AS Section
FROM foo

关键逻辑说明 #

• REGEXP_ESCAPE(Zone)：对Zone中的特殊字符（比如空格、潜在标点）转义，避免正则解析出错；
• CONCAT('^', REGEXP_ESCAPE(Zone), '\\s*')：生成开头匹配规则，^限定仅匹配字符串起始位置，\\s*匹配前缀后零个或多个空格，确保前缀与真实Section内容间的空格也被移除；
• TRIM()：清理替换后可能残留的首尾空格，保证结果整洁。

该方案自动适配所有场景：

1. Section完全以Zone为前缀的行，自动去除前缀及后续空格；
2. Section前缀与Zone不完全匹配的行，不做替换，保留原始内容；
3. Section与Zone无关的行，直接保留原始值。

内容的提问来源于stack exchange，提问作者Ben