BigQuery SQL处理含外层^G、内层空格分隔符列的需求

针对你描述的需求，这里提供BigQuery SQL的实现方案，核心思路是先拆分外层分隔符，提取每组的前后部分，再重新拼接：

首先明确：在BigQuery中，^G（ASCII分组分隔符）需要用\x1D来表示，直接输入该字符会有兼容性问题。

示例SQL代码 #

假设你的表名为your_table，待处理列名为input_col：

WITH numbered_rows AS (
  -- 给每行生成唯一标识，用于后续分组拼接
  SELECT
    ROW_NUMBER() OVER() AS row_id,
    input_col
  FROM your_table
),
split_elements AS (
  -- 按^G拆分每个输入值为单独的元素行
  SELECT
    row_id,
    element
  FROM numbered_rows,
  UNNEST(SPLIT(input_col, '\x1D')) AS element
)
SELECT
  -- 拼接第一部分（空格前的内容）
  STRING_AGG(REGEXP_EXTRACT(element, r'^(\S+)'), '\x1D') AS col_first,
  -- 拼接第二部分（空格后的内容）
  STRING_AGG(REGEXP_EXTRACT(element, r'^\S+\s+(.*)'), '\x1D') AS col_second
FROM split_elements
GROUP BY row_id;

代码说明 #

1. numbered_rows CTE：如果你的表没有唯一行ID，用ROW_NUMBER()生成临时标识，确保后续分组能对应回原行。
2. split_elements CTE：通过SPLIT和UNNEST将输入列按^G拆分成单个元素（比如a 11、b 22）。
3. 提取与拼接：
   • REGEXP_EXTRACT(element, r'^(\S+)')：匹配每个元素开头的非空白字符，提取空格前的内容（如a、b）。
   • REGEXP_EXTRACT(element, r'^\S+\s+(.*)')：匹配空格后的所有内容，即使后续包含空格也能完整提取（如11、22）。
   • STRING_AGG(..., '\x1D')：将提取的内容用^G重新拼接成目标格式的字符串。

测试验证 #

当输入列值为a 11^Gb 22^Gc 33（对应BigQuery中的a 11\x1Db 22\x1Dc 33），执行后会输出：

• col_first：a^Gb^Gc
• col_second：11^G22^G33

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