SQL从文本列解析日期时间：方法失效与逻辑疑问求解

问题背景 #

我有一个存储文本的ImportedBy列，需要从中提取日期时间数据。尝试用SUBSTRING、CHARINDEX和LEN组合提取，但部分场景失效；用找到的PatternSplitCM函数只能提取日期部分，拿不到时间戳。

示例数据与当前提取代码 #

DROP TABLE IF EXISTS #temptable;

CREATE TABLE #temptable
(
    ImportedBy VARCHAR(8000) NOT NULL
);

INSERT INTO #temptable
(
    ImportedBy
)
VALUES
('Tttttt Tooooon 2/3/2022 8:54:16 AM'),
('Pyyyyyy Vyyyyyy RN 1/24/2022 2:42:30 PM'),
('Jul 18 2022 11:34AM'),
('Jul 13 2022 10:32AM'),
('Meeeen Tooooo LPN 6/20/2022 3:57:15 PM'),
('Aaaaa Leeers RN 1/23/2023 9:48:21 AM');

SELECT t.ImportedBy,
       CASE
           WHEN TRY_CAST(t.ImportedBy AS DATETIME) IS NOT NULL THEN
               t.ImportedBy
           ELSE
               SUBSTRING(
                            TRIM(RIGHT(t.ImportedBy, 25)),
                            CHARINDEX(' ', TRIM(RIGHT(t.ImportedBy, 25))),
                            (LEN(TRIM(RIGHT(t.ImportedBy, 25))) - (CHARINDEX(' ', TRIM(RIGHT(t.ImportedBy, 25))))) + 1
                        )
       END AS [Scanned Date And Time],
        TRIM(RIGHT(t.ImportedBy, 25)) AS Step1,
        CHARINDEX(' ', TRIM(RIGHT(t.ImportedBy, 25))) AS Step2,
        (LEN(TRIM(RIGHT(t.ImportedBy, 25))) - (CHARINDEX(' ', TRIM(RIGHT(t.ImportedBy, 25))))) + 1 AS Step3,
       TRY_CAST(TRIM(ps.Item) AS DATE) AS ScannedDate
FROM #temptable AS t
    CROSS APPLY dbo.PatternSplitCM(t.ImportedBy, '[0-9/]') AS ps
WHERE ps.Matched = 1
      AND ps.Item LIKE '%[0-9]/[0-9]%';

遇到的问题 #

• 姓名后无头衔时解析正常，但带RN后缀时解析失败，带LPN后缀却无异常，原因不明。
• PatternSplitCM函数无法提取时间戳，不能作为完整解决方案。
• 对当前SUBSTRING语句中的Step2（CHARINDEX逻辑）、Step3（LEN与CHARINDEX的计算逻辑）存在疑问。

附：PatternSplitCM函数代码 #

-- Function by Chris Morris
CREATE FUNCTION dbo.PatternSplitCM
(
       @List                VARCHAR(8000) = NULL
       ,@Pattern            VARCHAR(50)
) RETURNS TABLE WITH SCHEMABINDING 
AS RETURN
    WITH numbers AS (
      SELECT TOP(ISNULL(DATALENGTH(@List), 0))
       n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
      FROM
      (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
      (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
      (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
      (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n))

    SELECT
      ItemNumber = ROW_NUMBER() OVER(ORDER BY MIN(n)),
      Item = SUBSTRING(@List,MIN(n),1+MAX(n)-MIN(n)),
      [Matched]
     FROM (
      SELECT n, y.[Matched], Grouper = n - ROW_NUMBER() OVER(ORDER BY y.[Matched],n)
      FROM numbers
      CROSS APPLY (
          SELECT [Matched] = CASE WHEN SUBSTRING(@List,n,1) LIKE @Pattern THEN 1 ELSE 0 END
      ) y
     ) d
     GROUP BY [Matched], Grouper;

解决方案与疑问解答 #

一、可行的提取方案 #

1. SQL Server 2017+：正则表达式直接匹配

利用REGEXP_REPLACE匹配两种目标日期格式，提取后转换为DATETIME：

SELECT 
    ImportedBy,
    TRY_CAST(
        REGEXP_REPLACE(
            ImportedBy,
            '^.*?(\d{1,2}/\d{1,2}/\d{4} \d{1,2}:\d{2}(:\d{2})? [APM]{2}|[A-Za-z]{3} \d{1,2} \d{4} \d{1,2}:\d{2}[APM]{2})',
            '$1'
        ) AS DATETIME
    ) AS [Scanned Date And Time]
FROM #temptable;

这个正则覆盖了两种格式：

• MM/DD/YYYY HH:MM:SS AM/PM（含不带秒的情况）
• MMM DD YYYY HH:MMAM/PM

2. 兼容低版本SQL Server：反向定位空格

通过反向查找倒数第二个空格的位置，锁定日期时间部分的起始点：

SELECT 
    ImportedBy,
    TRY_CAST(
        SUBSTRING(
            ImportedBy,
            -- 定位倒数第二个空格的位置，计算日期起始索引
            LEN(ImportedBy) - CHARINDEX(' ', REVERSE(ImportedBy), CHARINDEX(' ', REVERSE(ImportedBy)) + 1) + 2,
            LEN(ImportedBy)
        ) AS DATETIME
    ) AS [Scanned Date And Time]
FROM #temptable;

原理：日期时间始终是字段的最后一段（或带AM/PM的倒数第二段），通过反向查找空格可以准确跳过前面的姓名、头衔内容。

二、原SUBSTRING逻辑的疑问解答 #

• Step2（CHARINDEX逻辑）：CHARINDEX(' ', TRIM(RIGHT(t.ImportedBy, 25)))是找截取的25位字符串中第一个空格的位置，用来分割无关内容和日期。但当姓名后缀（如RN）导致截取的25位开头包含RN 时，这个空格会指向RN和日期之间的位置，而非日期的起始，导致提取内容错误。
• Step3（LEN与CHARINDEX计算）：LEN(...) - CHARINDEX(...) +1是计算从第一个空格到字符串末尾的长度，作为SUBSTRING的截取长度。如果第一个空格位置错误，这个长度对应的内容自然也不对。

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