时间戳

首页 > 解决方案 > 如何从文本字符串中获取日期？

sql - 如何从文本字符串中获取日期？

问题描述

我使用 Vertical SQL 并有一个字段“Note”，它是一个自由文本字段（输入数据的方式不一致）。我想创建另一个只有日期的字段或提取字段中的最后一个日期。例如

  1. “1st order on 3/2/21, second 5/5/21” -> “3/2/21 5/5/21” 或 “5/5/21”
  2. “第一次交货 2/2/21 第二次交货 8/30/21”->“2/2/21 8/30/21”或“8/30/21”
  3. “第 1 次报告：2/2/21。” ->“2/2/21”

谢谢！

标签： sqlstringdateextractvertica

解决方案

您可以使用REGEXP_SUBSTR()抓取模式：一位或多位数字；削减; 一位或多位数字；削减; 一位或多位数字。

如果您有多个这些模式，则为找到的每个模式创建一行作为输出。为此，CROSS JOIN使用一系列连续的整数，您可以输出模式的第 n 次出现。然后，将找到的字符串转换为 DATE。

最后，并且仅当您只需要最后一个日期时，应用 Vertica-peculiar分析限制子句，仅输出结果表i的相应id（我必须添加）的最大值。

WITH 
-- need a sequence of integers ...
i(i) AS (
          SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
)
,
indata(id,s) AS (
          SELECT 1,'1st order on 3/2/21, second 5/5/21'
UNION ALL SELECT 2,'first delivery 2/2/21 second one 8/30/21'
UNION ALL SELECT 3,'reported 1st: 2/2/21.'
)
SELECT
  id
, i
, s
, REGEXP_SUBSTR(s,'\d+/\d+/\d+',1,i) AS found_token
, REGEXP_SUBSTR(s,'\d+/\d+/\d+',1,i)::DATE AS found_date
FROM indata CROSS JOIN i
WHERE  REGEXP_SUBSTR(s,'(\d+/\d+/\d+)',1,i,'',1) <>''
-- remove the following line if you want all dates from all strings
-- and keep it if you only want the last date in the string
LIMIT 1 OVER(PARTITION BY id ORDER BY i DESC)
;
 id | i |                    s                     | found_token | found_date 
----+---+------------------------------------------+-------------+------------
  1 | 2 | 1st order on 3/2/21, second 5/5/21       | 5/5/21      | 2021-05-05
  2 | 2 | first delivery 2/2/21 second one 8/30/21 | 8/30/21     | 2021-08-30
  3 | 1 | reported 1st: 2/2/21.                    | 2/2/21      | 2021-02-02

推荐阅读