使用Google BigQuery SQL拆分字符串列且不拆分单词的方案

需求1：将字符串按单词拆分为多列 #

假设目标列的单词以空格分隔，可通过SPLIT函数将字符串转为数组，再通过OFFSET提取对应位置的单词作为独立列。若单词数量不足，可用IFNULL填充空值避免返回NULL。

示例代码：

SELECT
  text_col,
  SPLIT(text_col, ' ')[OFFSET(0)] AS word_col1,
  IFNULL(SPLIT(text_col, ' ')[OFFSET(1)], '') AS word_col2,
  IFNULL(SPLIT(text_col, ' ')[OFFSET(2)], '') AS word_col3,
  -- 根据实际需求扩展更多列
FROM `your_dataset.your_table`

需求2：将1500字符字符串拆分为每列264字符的多列（不拆分单词） #

通过递归CTE实现逐段截取：每次在前264字符范围内找到最后一个空格，以此为分割点截取片段；剩余字符串重复此逻辑，直到全部处理完成。最后通过行转列将片段转为多列。

示例代码：

WITH recursive_text_split AS (
  SELECT
    id, -- 替换为表的唯一标识字段
    text_col,
    1 AS segment_num,
    -- 截取第一段：优先取264字符内最后一个空格前的内容，无空格则取264字符
    CASE
      WHEN LENGTH(text_col) <= 264 THEN text_col
      ELSE SUBSTR(text_col, 1, 
        CASE 
          WHEN INSTR(REVERSE(SUBSTR(text_col, 1, 264)), ' ') = 0 THEN 264
          ELSE INSTR(REVERSE(SUBSTR(text_col, 1, 264)), ' ')
        END
      )
    END AS segment,
    -- 计算剩余未处理的字符串
    CASE
      WHEN LENGTH(text_col) <= 264 THEN ''
      ELSE SUBSTR(text_col, 
        CASE 
          WHEN INSTR(REVERSE(SUBSTR(text_col, 1, 264)), ' ') = 0 THEN 265
          ELSE INSTR(REVERSE(SUBSTR(text_col, 1, 264)), ' ') + 1
        END
      )
    END AS remaining_text
  FROM `your_dataset.your_table`

  UNION ALL

  SELECT
    id,
    text_col,
    segment_num + 1,
    CASE
      WHEN LENGTH(remaining_text) <= 264 THEN remaining_text
      ELSE SUBSTR(remaining_text, 1, 
        CASE 
          WHEN INSTR(REVERSE(SUBSTR(remaining_text, 1, 264)), ' ') = 0 THEN 264
          ELSE INSTR(REVERSE(SUBSTR(remaining_text, 1, 264)), ' ')
        END
      )
    END AS segment,
    CASE
      WHEN LENGTH(remaining_text) <= 264 THEN ''
      ELSE SUBSTR(remaining_text, 
        CASE 
          WHEN INSTR(REVERSE(SUBSTR(remaining_text, 1, 264)), ' ') = 0 THEN 265
          ELSE INSTR(REVERSE(SUBSTR(remaining_text, 1, 264)), ' ') + 1
        END
      )
    END AS remaining_text
  FROM recursive_text_split
  WHERE remaining_text != ''
)
-- 行转列生成多列
SELECT
  id,
  text_col,
  MAX(IF(segment_num = 1, segment, '')) AS segment_264_1,
  MAX(IF(segment_num = 2, segment, '')) AS segment_264_2,
  MAX(IF(segment_num = 3, segment, '')) AS segment_264_3,
  MAX(IF(segment_num = 4, segment, '')) AS segment_264_4,
  MAX(IF(segment_num = 5, segment, '')) AS segment_264_5,
  MAX(IF(segment_num = 6, segment, '')) AS segment_264_6 -- 1500/264≈5.68，最多需要6列
FROM recursive_text_split
GROUP BY id, text_col;

说明 #

• 若单词分隔符不是空格，只需将代码中的' '替换为实际分隔符（如逗号、分号等）。
• 递归CTE中的id字段用于关联原表数据，需替换为表中实际的唯一标识列（若无唯一标识，可使用GENERATE_UUID()临时生成）。

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