如何在SQL Server中从自由文本提取日期并转为mm/yyyy格式

核心思路 #

针对中文自由文本里的年月信息，利用年份必带“年”字这个特征，精准定位4位年份，再提取对应月份，最后格式化输出。核心是通过PATINDEX匹配特定模式，排除零散数字干扰。

自定义函数实现 #

创建一个可复用的标量函数，兼容“XXXX年X月”和“X月XXXX年”两种常见格式，同时处理月份补零：

CREATE FUNCTION dbo.ExtractMonthYear (@Text NVARCHAR(MAX))
RETURNS VARCHAR(7)
AS
BEGIN
    DECLARE @YearPos INT, @Year VARCHAR(4), @MonthPos INT, @Month VARCHAR(2)
    
    -- 定位带“年”后缀的4位年份（排除单个数字干扰）
    SET @YearPos = PATINDEX('%[0-9][0-9][0-9][0-9]年%', @Text)
    IF @YearPos = 0 RETURN NULL
    
    -- 提取年份
    SET @Year = SUBSTRING(@Text, @YearPos, 4)
    
    -- 处理“XXXX年X月”格式
    SET @MonthPos = PATINDEX('%月%', SUBSTRING(@Text, @YearPos + 5, LEN(@Text)))
    IF @MonthPos > 0
    BEGIN
        DECLARE @MonthStr NVARCHAR(MAX) = SUBSTRING(@Text, @YearPos + 5, @MonthPos - 1)
        -- 过滤出纯数字月份
        SET @MonthStr = LEFT(@MonthStr, PATINDEX('%[^0-9]%', @MonthStr + ' ') - 1)
        SET @Month = RIGHT('0' + @MonthStr, 2)
        RETURN @Month + '/' + @Year
    END
    
    -- 处理“X月XXXX年”格式
    SET @MonthPos = PATINDEX('%[0-9]月%', LEFT(@Text, @YearPos - 1))
    IF @MonthPos > 0
    BEGIN
        DECLARE @MonthStr2 NVARCHAR(MAX) = SUBSTRING(LEFT(@Text, @YearPos - 1), @MonthPos, PATINDEX('%月%', LEFT(@Text, @YearPos - 1)) - @MonthPos)
        SET @MonthStr2 = LEFT(@MonthStr2, PATINDEX('%[^0-9]%', @MonthStr2 + ' ') - 1)
        SET @Month = RIGHT('0' + @MonthStr2, 2)
        RETURN @Month + '/' + @Year
    END
    
    RETURN NULL
END

使用示例 #

-- 测试数据
DECLARE @TestTable TABLE (ID INT, FreeText NVARCHAR(MAX))
INSERT INTO @TestTable VALUES
(1, '我5岁时的最后日期是2020年5月'),
(2, '1999年5月我在那里'),
(3, '2017年12月毕业'),
(4, '是6月2021年入职的')

-- 提取并格式化日期
SELECT 
    ID,
    FreeText,
    dbo.ExtractMonthYear(FreeText) AS FormattedDate
FROM @TestTable

输出结果：

扩展：处理中文月份 #

如果文本里有“五月”“十二月”这类中文月份，可在函数中加入中文月份映射逻辑：

-- 在函数内添加映射表（或创建全局映射表）
DECLARE @MonthMap TABLE (CNMonth NVARCHAR(3), NumMonth VARCHAR(2))
INSERT INTO @MonthMap VALUES
('一月','01'),('二月','02'),('三月','03'),('四月','04'),('五月','05'),
('六月','06'),('七月','07'),('八月','08'),('九月','09'),('十月','10'),
('十一月','11'),('十二月','12')

-- 替换原有的数字月份提取逻辑，匹配中文月份
SET @MonthPos = PATINDEX('%[一二三四五六七八九十]月%', SUBSTRING(@Text, @YearPos + 5, LEN(@Text)))
IF @MonthPos > 0
BEGIN
    DECLARE @CNMonth NVARCHAR(3) = SUBSTRING(@Text, @YearPos + 5, CHARINDEX('月', SUBSTRING(@Text, @YearPos + 5, LEN(@Text))))
    SELECT @Month = NumMonth FROM @MonthMap WHERE CNMonth = @CNMonth
    RETURN @Month + '/' + @Year
END

注意事项 #

• 函数仅提取第一个匹配的年月，若文本含多个日期，需修改为循环提取逻辑。
• 若存在非标准格式（如“2020.5”），需补充对应PATINDEX匹配规则。

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