在Cosmos DB中提取字符串字符第n次出现索引及RegexMatch报错问题

我希望根据字符的出现位置提取输入字符串的不同部分。使用SUBSTRING和INDEX_OF函数，我已成功提取出字符串的第一部分，代码如下：

select 'abc_def_ghij_20230108100000_20230108100000_20240517105347' as input,
INDEX_OF('abc_def_ghij_20230108100000_20230108100000_20240517105347', '_'),
substring('abc_def_ghij_20230108100000_20230108100000_20240517105347', 0, INDEX_OF('abc_def_ghij_20230108100000_20230108100000_20240517105347', '_')) as 'first_part'

接下来，我尝试将RegExMatch函数融入INDEX_OF，以获取字符串中数字开始出现的索引，但写法有误，代码如下：

select 'abc_def_ghij_20230108100000_20230108100000_20240517105347' as input,
INDEX_OF('abc_def_ghij_20230108100000_20230108100000_20240517105347', '_'),
SUBSTRING('abc_def_ghij_20230108100000_20230108100000_20240517105347', 0, INDEX_OF('abc_def_ghij_20230108100000_20230108100000_20240517105347', '_')) as 'first_part',
substring('abc_def_ghij_20230108100000_20230108100000_20240517105347', INDEX_OF('abc_def_ghij_20230108100000_20230108100000_20240517105347', '_'), INDEX_OF('abc_def_ghij_20230108100000_20230108100000_20240517105347', RegexMatch("abc_def_ghij_20230108100000_20230108100000_20240517105347", "[0-9]"))) as 'date_part'

请问我的RegExMatch用法哪里出错了？

你的核心错误是对RegExMatch的返回值认知错误：

• RegExMatch的作用是返回匹配到的字符串内容（比如你用[0-9]会返回第一个数字字符2），而不是匹配位置的索引。你把它的返回值传给INDEX_OF，相当于让INDEX_OF查找单个数字字符的位置，这完全偏离了“获取第一个数字起始索引”的需求，还会导致SUBSTRING的参数逻辑混乱。

正确实现方式 #

要获取第一个数字的起始索引，应该用专门返回匹配位置的正则函数，不同SQL方言对应函数不同：

• Spark SQL用regexp_position
• Hive/Oracle/MySQL 8.0+用REGEXP_INSTR

以Spark SQL为例，优化后的代码如下（用别名简化重复字符串）：

select 
    'abc_def_ghij_20230108100000_20230108100000_20240517105347' as input,
    INDEX_OF(input, '_') as first_underscore_pos,
    substring(input, 0, INDEX_OF(input, '_')) as first_part,
    -- 获取第一个数字的起始位置
    regexp_position(input, '[0-9]') as first_digit_pos,
    -- 提取所有日期部分
    substring(input, first_digit_pos) as all_date_parts,
    -- 提取第一个14位日期段
    substring(input, first_digit_pos, 14) as first_date_part

如果你的SQL环境不支持这类函数，可以通过正则替换间接计算位置：

select 
    input,
    first_part,
    -- 计算第一个数字的起始索引：原字符串长度减去去掉非数字前缀后的字符串长度
    length(input) - length(regexp_replace(input, '^[^0-9]+', '')) as first_digit_pos,
    substring(input, length(input) - length(regexp_replace(input, '^[^0-9]+', ''))) as all_date_parts
from (
    select 
        'abc_def_ghij_20230108100000_20230108100000_20240517105347' as input,
        substring(input, 0, INDEX_OF(input, '_')) as first_part
) t

额外优化 #

把重复的原字符串用别名代替，避免重复编写，提升代码可读性和维护性。

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