如何在Snowflake中实现SQL Server PATINDEX对应的字符串拆分逻辑？

问题背景 #

原SQL Server通过PATINDEX从proddetails表的Filename字段提取前缀（filename_U）和日期（filedate_U），迁移到Snowflake时使用REGEXP_INSTR未得到正确结果，需要修正查询语句。

原SQL Server代码 #

CREATE TABLE [dbo].[proddetails](
    [Filename] [varchar](50) NULL,
    [pid] [int] NULL
) 
INSERT [dbo].[proddetails] ([Filename], [pid]) VALUES (N'cinthol_20200108.csv', 1)
INSERT [dbo].[proddetails] ([Filename], [pid]) VALUES (N'pencame_20220309_1.csv', 2)
INSERT [dbo].[proddetails] ([Filename], [pid]) VALUES (N'prodct_20220403.csv', 3)
INSERT [dbo].[proddetails] ([Filename], [pid]) VALUES (N'jain_rav_pan_20220109_1.csv', 4)

SELECT pid, filename,
       SUBSTRING(filename,0,PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',filename)) filename_U,
       CAST(CAST(SUBSTRING(filename,PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',filename),8) AS varchar(8)) AS date) filedate_U
FROM [test].[dbo].[proddetails]

期望输出 #

用户错误的Snowflake查询 #

SELECT pid, filename,
       SUBSTRING(filename,0,REGEXP_INSTR('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',filename)) filename_U,
       CAST(CAST(SUBSTRING(filename,REGEXP_INSTR('%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',filename),8) AS varchar(8)) AS date) filedate_U
FROM proddetails 

正确的Snowflake查询语句 #

首先创建Snowflake表并插入数据：

CREATE TABLE proddetails(
    Filename VARCHAR(50),
    pid INT
);

INSERT INTO proddetails (Filename, pid) 
VALUES 
    ('cinthol_20200108.csv', 1),
    ('pencame_20220309_1.csv', 2),
    ('prodct_20220403.csv', 3),
    ('jain_rav_pan_20220109_1.csv', 4);

然后执行提取逻辑：

SELECT 
    pid,
    filename,
    -- 提取前缀：取日期前的所有字符
    LEFT(filename, REGEXP_INSTR(filename, '[0-9]{8}') - 1) AS filename_U,
    -- 提取8位日期并转换为DATE类型
    TO_DATE(REGEXP_SUBSTR(filename, '[0-9]{8}'), 'YYYYMMDD') AS filedate_U
FROM proddetails;

关键修改点说明 #

1. REGEXP_INSTR参数顺序修正：Snowflake中REGEXP_INSTR的语法是REGEXP_INSTR(输入字符串, 正则表达式)，用户原查询把参数顺序写反了。
2. 正则表达式简化：用[0-9]{8}代替重复8次的[0-9]，匹配连续8位数字，更简洁。
3. 字符串索引差异处理：Snowflake的字符串函数是1-based索引，而SQL Server是0-based。原SQL Server用SUBSTRING(filename,0, ...)取前缀，对应Snowflake用LEFT函数取到匹配位置减1的字符。
4. 日期提取优化：直接用REGEXP_SUBSTR提取8位数字，再通过TO_DATE指定格式转换，比嵌套SUBSTRING和CAST更高效直观。

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