代码之家 › 专栏 › 技术社区 › DRT

从文本字段中提取多个日期

tsql sql-server-2008

DRT · 技术社区 · 6 年前

我使用了一个SQL查询,它可以很好地满足提取第一文本字段中的日期。这是一个自由格式的文本字段,与状态关联,当正确的条件对齐时,我的查询会查找日期,如果格式正确,则会提取该日期。

有时,日期将作为一系列日期或以逗号分隔的日期列表输入。我想知道,如果是日期范围,是否有方法提取最后一个日期,或者是日期列表中的其他日期?

当前查询在临时表中有3个提取日期的步骤。这里是每个步骤的代码片段。

在第一步中,它查找“proposed”一词,并在以下内容后获取一些字符:

,SUBSTRING(al.Comments,
    PATINDEX('%proposed%',al.Comments)+9,17)        [DateFirstPass]

在第二步中,查询将提取日期:

,LEFT(
    SUBSTRING(p1.DateFirstPass, PATINDEX('%[0-9/]%', p1.DateFirstPass), 10), --string (only numbers and forward slash) MAX of 10 chars (mm/dd/yyyy)
            PATINDEX('%[^0-9/]%', SUBSTRING(p1.DateFirstPass, PATINDEX('%[0-9/]%', p1.DateFirstPass), 10) + 'X')-1) --char length after negating nonvalid characters '%[^0-9/]%'
                                                    [DateSecondPass]

在最后一关中,如果缺少,则在当年添加:

    CASE    
        WHEN ISDATE(p2.DateSecondPass) = 1
        THEN CAST(p2.DateSecondPass AS DATE)
        WHEN ISDATE(p2.DateSecondPass + '/' + this.yr) = 1
        THEN p2.DateSecondPass + '/' + this.yr --Adds missing year
    END                                             [DateThirdPass]
    FROM
        #ProposedDateParse2 p2
        CROSS JOIN (VALUES (CAST(YEAR(GETDATE()) AS varchar(4)))) this(yr)

1 回复 | 直到 6 年前

Alan Burstein 6 年前

获取的副本 patternSplitCM

代码如下:

-- PatternSplitCM will split a string based on a pattern of the form 
-- supported by LIKE and PATINDEX 
-- 
-- Created by: Chris Morris 12-Oct-2012 
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

针对变量的解决方案:

DECLARE @string varchar(8000) = 
'Blah blah 3/1/2017, 12/19/2018,1/2/2020,1111/11111/1111/111/111 blah blah';

SELECT TOP (1) Item
FROM dbo.patternSplitCM(@string, '[0-9/]')
WHERE [Matched] = 1 AND ISDATE(item) = 1
ORDER BY -ItemNumber;

结果:

item
------
1/2/2020

表格示例:

DECLARE @table table (someid int identity, sometext varchar(1000));
INSERT @table(sometext) VALUES
('Blah blah 3/1/2017, 12/19/2018,1/2/2020,1111/11111/1111/111/111 blah blah'),
('Yada yada 1/1/12, 12/31/1999 call me at 555-1212!');

SELECT t.someid, getLastDate.Item
FROM @table t
CROSS APPLY 
(
  SELECT TOP (1) Item
  FROM dbo.patternSplitCM(t.sometext, '[0-9/]')
  WHERE [Matched] = 1 AND ISDATE(item) = 1
  ORDER BY -ItemNumber
) getLastDate;

结果:

someid      Item
----------- -----------
1           1/2/2020
2           12/31/1999