Google Spanner SQL正则表达式优化：提取指定区间内容需求

我来帮你搞定这个正则提取的问题！针对你的需求，我们可以通过调整原方案1的匹配逻辑，精准捕获你需要的"50"和"PQ"，同时避免之前的过度匹配问题。

原方案1的问题分析 #

你之前用的:([^:]*)-之所以会匹配出过长的内容，核心原因是[^:]*是贪婪匹配规则——它会匹配所有非冒号的字符，包括字符串里后续的-和其他内容，直到遇到最后一个-（第一个示例字符串里有两个-），所以才会得到: 050-G&H Sample -这类不符合预期的结果。

优化后的解决方案 #

我们可以分两步实现精准提取，或者用一步到位的正则直接完成，两种方式都能满足你的需求：

方式1：分步提取（更直观易维护）

第一步：捕获冒号后到第一个-的目标子串

先调整正则，只匹配第三个冒号后到第一个-之间的连续非空白字符（你的目标内容都是无空格的连续串），正则表达式为：

: (\S+)-

• : 精准匹配第三个冒号后的空格（和你的字符串格式完全对应）
• (\S+) 捕获一个或多个非空白字符（也就是050或PQ）
• - 匹配目标子串后的第一个连字符，终止匹配范围

在Google Spanner中，用REGEXP_EXTRACT提取这个子串：

REGEXP_EXTRACT(your_column, ': (\S+)-')

第二步：处理数字子串的前导零

对于提取到的050，我们需要去掉前导零；而PQ这类字母串直接保留。用CASE结合正则替换就能实现：

CASE
  -- 判断提取的子串是否为纯数字
  WHEN REGEXP_CONTAINS(extracted_substr, '^\d+$') THEN REGEXP_REPLACE(extracted_substr, '^0+', '')
  ELSE extracted_substr
END AS final_result

• REGEXP_CONTAINS(..., '^\d+$') 检查子串是否是纯数字格式
• REGEXP_REPLACE(..., '^0+', '') 移除开头的所有前导零

完整SQL示例

把两步结合起来，用示例数据测试的完整代码如下：

WITH sample_data AS (
  SELECT "ABC : DEP : 050-G&H Sample - IJ" AS input_str UNION ALL
  SELECT "ABC : DEP : PQ-Word1 Word2 Word3" AS input_str
)
SELECT
  input_str,
  CASE
    WHEN REGEXP_CONTAINS(extracted_substr, '^\d+$') THEN REGEXP_REPLACE(extracted_substr, '^0+', '')
    ELSE extracted_substr
  END AS target_result
FROM (
  SELECT
    input_str,
    REGEXP_EXTRACT(input_str, ': (\S+)-') AS extracted_substr
  FROM sample_data
);

执行后会得到预期结果：

方式2：一步到位的正则（更简洁）

如果你想只用一个正则直接提取目标内容，可以用分支匹配的写法，直接捕获去掉前导零的数字或字母串：

: (?:0*([1-9]\d+)|([A-Za-z]+))-

然后用REGEXP_EXTRACT提取即可，Spanner会自动返回第一个非空的捕获组：

REGEXP_EXTRACT(input_str, ': (?:0*([1-9]\d+)|([A-Za-z]+))-')

这个正则的逻辑是：

• (?:...) 是非捕获组，用来包裹两个匹配分支
• 第一个分支0*([1-9]\d+)：匹配任意数量的前导零，捕获从非零数字开始的有效数字部分
• 第二个分支([A-Za-z]+)：直接捕获所有连续字母串

这样也能直接得到50和PQ，不需要额外的CASE语句。

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