BigQuery中如何按字符位置而非分隔符拆分字符串？

问题分析 #

你用split函数后Column2为空，大概率是原字符串里的空白字符不是普通空格（比如全角空格、制表符），或者存在多个连续空白字符导致拆分异常。不过既然你已经明确字符串的格式是固定的，按字符位置截取是更可靠的方案，完全匹配你的需求。

方案1：按固定字符位置截取 #

根据你给出的位置规则（假设字符串索引从0开始），可以用字符串截取函数直接提取各字段：

示例SQL（以BigQuery为例） #

SELECT
  SUBSTR(Column1, 0, 12) AS `column 1`,
  SUBSTR(Column1, 13, 1) AS `column 2`,
  SUBSTR(Column1, 14, 4) AS `column 3`,
  SUBSTR(Column1, 18, 2) AS `column 4`
FROM
  your_table;

不同引擎的语法调整 #

• 如果是MySQL（索引从1开始）：

SELECT
  SUBSTR(Column1, 1, 12) AS `column 1`,
  SUBSTR(Column1, 14, 1) AS `column 2`,
  SUBSTR(Column1, 15, 4) AS `column 3`,
  SUBSTR(Column1, 19, 2) AS `column 4`
FROM
  your_table;

• 如果是Python：

def split_row(s):
    return {
        'column 1': s[0:12],
        'column 2': s[13],
        'column 3': s[14:18],
        'column 4': s[18:20]
    }

方案2：修复split函数的问题 #

如果坚持用拆分方式，先统一空白字符为单个空格再拆分：

示例SQL（BigQuery） #

SELECT
  SPLIT(REGEXP_REPLACE(Column1, r'\s+', ' '), ' ')[OFFSET(0)] AS `column 1`,
  SUBSTR(SPLIT(REGEXP_REPLACE(Column1, r'\s+', ' '), ' ')[OFFSET(1)], 0, 1) AS `column 2`,
  SUBSTR(SPLIT(REGEXP_REPLACE(Column1, r'\s+', ' '), ' ')[OFFSET(1)], 1, 4) AS `column 3`,
  SUBSTR(SPLIT(REGEXP_REPLACE(Column1, r'\s+', ' '), ' ')[OFFSET(1)], 5, 2) AS `column 4`
FROM
  your_table;

这里先用REGEXP_REPLACE把所有连续空白字符替换成单个空格，再拆分，然后对第二个拆分结果继续截取子串。

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