如何使用SQL语句从文件名中提取位置不固定的英文月日年日期

需求背景 #

需要通过SQL从文件名中提取目标日期，文件名无统一整体结构，但均满足以下特征：

• 所有文件名必然包含英文格式日期，分为两类：英文月份名 日期 四位年份（如May 18 2022）、英文月份名 四位年份（如November 2021）
• 日期在文件名中的位置不固定，文件名中固定包含公司标识字符串AAAA
• 文件名中可能额外存在_20220518这类数字格式的日期串，不属于提取目标

待处理文件名样例 #

• AAAA May 18 2022 - User List - AAAA user list_20220518_1858.csv
• AAAA User Report September 1 2021 - AAAA user list.csv
• November 2021 - AAAA User List - AAAA Main - User List.csv
• AAAA May 18 2022 - User List - AAAA user list_20220518_1858_20220616.csv
• October 2021 - AAAA User List - AAAA - Cybersource user list (4).csv
• AAAA May 18 2022 - User List - AAAA user list_20220518_1858.csv

现有方案缺陷 #

已编写的SQL仅能适配特定前缀、特定分隔符的场景，通用性极差：

DECLARE @FileName VARCHAR(100) ='AAAA User Report September 1 2021 - AAAA user list.csv'
SELECT cast(SUBSTRING(replace(@FileName, 'AAAA User Report', ''), 1, CHARINDEX('-', replace(@FileName, 'AAAA User Report', ''), 1) - 2) AS datetime) AS FileDate

DECLARE @FileName VARCHAR(100) ='November 2021 - AAAA User List - AAAA Main - User List.csv'
SELECT cast(SUBSTRING(replace(@FileName, 'AAAA User List', ''), 1, CHARINDEX('-', replace(@FileName, 'AAAA User List', ''), 1) - 2) AS datetime) AS FileDate

以上代码依赖固定字符串替换、-分隔符定位日期，一旦文件名结构变动、日期位置偏移就会提取失败，需要一套不依赖固定格式、不限制日期位置的通用实现。

通用实现方案 #

核心思路是通过正则匹配英文月份开头的日期串，优先匹配带具体日期的长格式，避免误识别文件名中_20220518这类无月份的数字日期串，匹配成功后直接转换为日期类型即可。

1. MySQL 8.0+/PostgreSQL 等支持原生正则的数据库 #

直接用正则提取函数实现，代码最简洁：

-- MySQL 8.0 版本示例
SELECT 
    FileName,
    STR_TO_DATE(
        COALESCE(
            -- 先匹配「月 日 年」完整格式
            REGEXP_SUBSTR(FileName, '(January|February|March|April|May|June|July|August|September|October|November|December) [0-9]{1,2} [0-9]{4}'),
            -- 完整格式匹配失败则匹配「月 年」格式，默认补1号
            CONCAT('1 ', REGEXP_SUBSTR(FileName, '(January|February|March|April|May|June|July|August|September|October|November|December) [0-9]{4}'))
        ),
        '%M %e %Y'
    ) AS ExtractedFileDate
FROM your_file_table

2. SQL Server 版本（适配原有语法环境） #

SQL Server原生PATINDEX不支持正则或语法，可通过自定义函数实现，无需依赖CLR组件：

CREATE FUNCTION dbo.ExtractDateFromFileName(@FileName VARCHAR(500))
RETURNS DATETIME
AS
BEGIN
    DECLARE @Months TABLE(MonthName VARCHAR(20) PRIMARY KEY)
    INSERT INTO @Months VALUES
    ('January'),('February'),('March'),('April'),('May'),('June'),
    ('July'),('August'),('September'),('October'),('November'),('December')
    
    DECLARE @MonthName VARCHAR(20), @MonthPos INT, @SearchStart INT = 1
    DECLARE @TargetStr VARCHAR(50), @Result DATETIME

    -- 遍历所有月份名找匹配位置
    WHILE @SearchStart <= LEN(@FileName)
    BEGIN
        SELECT TOP 1 @MonthName = MonthName, @MonthPos = PATINDEX('%' + MonthName + '%', SUBSTRING(@FileName, @SearchStart, LEN(@FileName)))
        FROM @Months
        WHERE PATINDEX('%' + MonthName + '%', SUBSTRING(@FileName, @SearchStart, LEN(@FileName))) > 0
        ORDER BY PATINDEX('%' + MonthName + '%', SUBSTRING(@FileName, @SearchStart, LEN(@FileName)))

        IF @MonthName IS NULL BREAK
        SET @MonthPos = @MonthPos + @SearchStart - 1
        SET @TargetStr = SUBSTRING(@FileName, @MonthPos, 20)

        -- 先尝试匹配月+日+年格式
        SET @Result = TRY_CAST(LEFT(@TargetStr, PATINDEX('%[12][0-9][0-9][0-9]%', @TargetStr)+3) AS DATETIME)
        IF @Result IS NOT NULL RETURN @Result

        -- 再尝试匹配月+年格式，补1号
        SET @Result = TRY_CAST('1 ' + LEFT(@TargetStr, PATINDEX('%[12][0-9][0-9][0-9]%', @TargetStr)+3) AS DATETIME)
        IF @Result IS NOT NULL RETURN @Result

        SET @SearchStart = @MonthPos + LEN(@MonthName)
        SET @MonthName = NULL
    END

    RETURN NULL
END
GO

函数调用方式：

SELECT 
    FileName,
    dbo.ExtractDateFromFileName(FileName) AS ExtractedFileDate
FROM your_file_table

方案特性 #

• 不依赖固定文件名前缀、分隔符，日期出现在文件名任意位置都可正常提取
• 优先匹配带具体日的完整日期，不会误识别文件名末尾_20220518这类数字串
• 带容错逻辑，匹配失败返回NULL，不会因异常文件名报错
• 仅包含月份和年份的文件默认返回当月1日，可根据业务需求调整为当月最后一日

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