sql - 如何在SQL中将字符串添加到正则表达式中，然后将其转换为日期？

Question

我有一个表，其中一列包含以下格式的字符串:

Info
ENGLISH.JOHN.BROWN.MAR.92
GERMAN.TIM.OLIVER.JAN.95

我使用正则表达式将字符串末尾的 3 个字母月份和 2 位数字年份转换为日期。但是，我需要日期中的日期是每个月的 15 日，然后使用该日期计算与今天的日差。下面是我的代码:

  SELECT (TO_DATE(('15.'+(REGEXP_SUBSTR(Info, '[A-Z]{3}\.\d{2}'))), 'DD.MON.RR')-to_date('17-SEP-19', 'DD-MON-YY')) AS DAY_DIFF FROM my_table 

我收到一条错误消息，指出数字无效。您能告诉我在连接字符串时我做错了什么吗？

我正在使用 Toad for Oracle，我的实际表有数百行，因此我需要如上所示转换每一行。

Answer 1

您可以使用SUBSTR( Info, -6 ) 获取字符串的最后 6 个字符。 TO_DATE( value, 'MON.RR' ) 将转换为每月第一天的日期(但是，由于您使用的是缩写的月份名称，因此最好还指定 NLS_DATE_LANGUAGE 您正在使用，因此它不会默认为用户的 session 值)。然后，您可以向其中添加 14 天，并找出与 TRUNC( SYSDATE ) 的差异(今天的日期被截断回午夜)。

像这样:

Oracle 设置:

CREATE TABLE my_table ( Info ) As
SELECT 'ENGLISH.JOHN.BROWN.MAR.92' FROM DUAL UNION ALL
SELECT 'GERMAN.TIM.OLIVER.JAN.95'  FROM DUAL

查询:

SELECT ( TO_DATE(
           SUBSTR( info, -6 ),
           'MON.RR',
           'NLS_DATE_LANGUAGE = AMERICAN'
         ) + INTERVAL '14' DAY
       ) AS parsed_date,
       TRUNC( SYSDATE ) -
       ( TO_DATE(
           SUBSTR( info, -6 ),
           'MON-RR',
           'NLS_DATE_LANGUAGE = AMERICAN'
         ) + INTERVAL '14' DAY
       )
       AS day_diff
FROM   my_table

输出:

PARSED_DATE | DAY_DIFF:---------- | -------:15-MAR-92   |    1004715-JAN-95   |     9011

db<> fiddle here