如何使用TSQL for Server从文本字段中提取日期

Question

我正在尝试从文本字段中提取日期。我用PADINDEX来做这件事。我的结果很不一致。不管怎么说，我可以通过SQL来完成这个任务。我没有申请为我做这件事。我正试图得到这份报告，这是必要的。

文本字段的日期并不总是被列出为MM/DD/YYYY，有时它被列出为M/DD/YYYY。另外，

下面是我使用的查询：

select
substring(ar.finding_text,patindex('%[0-9]%/[0-9][0-9]/[0-9][0-9][0-9][0-9]%',ar.finding_text),10)

FROM [ARKPPDB].[PowerPath].[dbo].[accession_2] a
LEFT OUTER JOIN acc_results ar on a.id = ar.acc_id

Answer 1

我把这个搞定了。谢谢你们的帮助。

select 
*,
LTRIM(RTRIM(REPLACE(REPLACE(IIF(LTRIM(RTRIM(RIGHT(SUBSTRING([Time Client 
Called], CHARINDEX('on',[Time Client Called]), 15),13))) is null, null, 
LTRIM(RTRIM(RIGHT(SUBSTRING([Time Client Called], CHARINDEX('on',[Time 
Client Called]), 15),13)))), 'a', ''),'t','')))  as "Date Client Called",

IIF(LTRIM(RTRIM(RIGHT(SUBSTRING([Time Client Called], CHARINDEX('at',[Time 
Client Called]), 11),8))) is null, null, LTRIM(RTRIM(RIGHT(SUBSTRING([Time 
Client Called], CHARINDEX('at',[Time Client Called]), 11),8))))  as "Time 
Called"

into #tmpCalls
FROM #tmpPattern

Answer 2

您可以添加这样的模式“优先级”：

DECLARE @Patterns TABLE(Pattern VARCHAR(100),PatternLength INT)

INSERT INTO @Patterns(Pattern, PatternLength)
VALUES('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] [0-9][0-9]:[0-9][0-9] [AP]M%',19),
    ('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] [0-9]:[0-9][0-9] [AP]M%',18),
    ('%[0-9][0-9]/[0-9]/[0-9][0-9][0-9][0-9] [0-9][0-9]:[0-9][0-9] [AP]M%',18),
    ('%[0-9][0-9]/[0-9]/[0-9][0-9][0-9][0-9] [0-9]:[0-9][0-9] [AP]M%',17),
    ('%[0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] [0-9][0-9]:[0-9][0-9] [AP]M%',18),
    ('%[0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] [0-9]:[0-9][0-9] [AP]M%',17),
    ('%[0-9]/[0-9]/[0-9][0-9][0-9][0-9] [0-9][0-9]:[0-9][0-9] [AP]M%',17),
    ('%[0-9]/[0-9]/[0-9][0-9][0-9][0-9] [0-9]:[0-9][0-9] [AP]M%',16)

SELECT IIF(pat1.Pattern is null or LEN(replace(upper(ar.finding_text),' AT ',' ')) < patindex(pat1.Pattern,replace(upper(ar.finding_text),' AT ',' '))+pat1.PatternLength, null,substring(replace(UPPER(ar.finding_text),' AT ',' '),patindex(pat1.Pattern,replace(upper(ar.finding_text),' AT ',' ')),pat1.PatternLength))
FROM [ARKPPDB].[PowerPath].[dbo].[accession_2] a
LEFT OUTER JOIN acc_results ar on a.id = ar.acc_id
LEFT OUTER JOIN @Patterns pat1 on patindex(pat1.Pattern,replace(upper(ar.finding_text),' AT ',' ')) > 0
LEFT OUTER JOIN @Patterns pat2 on patindex(pat2.Pattern,replace(upper(ar.finding_text),' AT ',' ')) > 0 and pat2.PatternLength > pat1.PatternLength
WHERE pat2.Pattern IS NULL

因此，一些示例文本：

医生/医师办公室于2022年2月1日下午3:27致电中央

这应该返回'2/1/2022 3:27下午‘，您应该能够转换成一个日期时间。我没有麻烦中央。您展示的每个示例都包含Central，因此您可能只是假设它是中央时间。

Answer 3

我从另一个线程获得了关于这篇文章的更多信息，包括一些数据的实际外观的提示。下面是我创建的响应的代码部分，包括测试数据(如果有人想玩的话)。

   DROP TABLE IF EXISTS #TestTable;
GO
--===== Create and populate the test table from the data provided.
     -- This is NOT a part of the solution. We're just creating test data here.
 SELECT *
   INTO #TestTable
   FROM (VALUES
         ('Physician/Physician’s office called on 2/1/2022 at 3:27 PM Central.')
        ,('Physician/Physician’s office called on 2/1/2022 at 3:34 PM Central.')
        ,('Physician/Physician’s office called on 2/1/2022 at 2:47 PM Central.')
        ,('Physician/Physician’s office called on 2/1/2022 at 4:17 PM Central.')
        ,('Physician/Physician’s office called on 2/1/2022 at 2:52 PM Central.')
        ,('Physician/Physician’s office called on 2/1/2022 at 2:51 PM Central.')
        ,('Physician/Physician’s office called on 2/1/2022 at 4:17 PM Central.')
        ,('Physician/Physician’s office called on 2/1/2022 at 4:34 PM Central.')
        ,('Physician/Physician’s office called on 2/1/2022 at 11:49 PM Eastern.')
        ,('Physician/Physician’s office called on 2/1/2022 at 11:27 AM Eastern.')
        ,('Physician/Physician’s office called on 2/1/2022 at 11:34 AM Eastern.')
        ,('Physician/Physician’s office called on 2/1/2022 at 10:47 AM Central.')
        ,('Physician/Physician’s office called on 2/1/2022 at 9:17 AM Mountain.')
        ,('Physician/Physician’s office called on 2/1/2022 at 10:52 AM Central.')
        ,('Physician/Physician’s office called on 2/1/2022 at 10:51 AM Central.')
        ,('Physician/Physician’s office called on 2/1/2022 at 9:17 AM Pacific.')
        ,('Physician/Physician’s office called on 2/1/2022 at 9:34 AM Pacific.')
        ,('Physician/Physician’s office called on 2/1/2022 at 11:49 AM Somewhere over the rainbow.')
        )v(SomeString)
;
--===== Let's see what we've got
 SELECT * FROM #TestTable
;
--===== Solve the given problem.
     -- So long as the following pattern is true in the source data, this should always work.
     --     WhoCalled by the string ' called on ' to determine the caller followed by...
     --     by a date string followed by the string ' at ' followed by a meridian time (Has AM/PM) for the CallDT followed by...
     --     a time zone name string for the TimeZone.
     -- The result is available as a DATETIME2(0) with no decimal seconds.
 SELECT SomeString
        ,WhoCalled  = LEFT(SomeString,ca1.CalledOn-1)
        ,CallDT     = CONVERT(DATETIME2(0),REPLACE(SUBSTRING(SomeString,ca1.CalledOn+10,ca2.Meridian-CalledOn-9),'at',''))
        ,TimeZone   = TRIM(' .' FROM SUBSTRING(SomeString,ca2.Meridian+2,500))  
   FROM #TestTable
  CROSS APPLY (VALUES(CHARINDEX(' called on ',SomeString)))ca1(CalledOn)
  CROSS APPLY (VALUES(PATINDEX('% [AP]M %',SomeString)+2))ca2(Meridian)
;
GO