如何从源文件名中提取日期？附SQL实现语句及示例文件名

如果需要从类似OEA_CustomerOnboarded_Czech_20230208085230.JSON的源文件名里提取日期时间并转换为时间戳格式，可以用以下SQL语句实现：

to_timestamp_ntz(substring(SOURCE_OF_RECORD,REGEXP_INSTR(SOURCE_OF_RECORD, '.json')-14,14),'yyyymmddhh24miss') AS EXTRACTED_AT

语句逻辑拆解 #

• REGEXP_INSTR(SOURCE_OF_RECORD, '.json')：定位文件名中.json（不区分大小写，适配示例里的.JSON）的起始位置
• 减去14是因为目标日期串是固定14位的yyyymmddhh24miss格式（4位年份+2位月份+2位日期+2位小时+2位分钟+2位秒），以此得到日期串的起始索引
• substring(SOURCE_OF_RECORD, 起始索引, 14)：从文件名中精准截取14位的日期时间字符串
• to_timestamp_ntz(..., 'yyyymmddhh24miss')：将截取到的字符串转换为不带时区的时间戳，最终字段命名为EXTRACTED_AT

效果示例 #

针对文件名OEA_CustomerOnboarded_Czech_20230208085230.JSON，执行该语句后会提取出时间戳2023-02-08 08:52:30。

内容的提问来源于stack exchange，提问作者Md Shahnawaz Alam