如何创建甲骨文减号分隔的日期时间格式正则表达式?

Question

我的表有几列。此示例中最重要的是其中的 2 个。我在数据库行中有以下字符串：

+----+--------------------------------------------------------------------------------------+-------+
| id |                                         info                                         | value |  
+----+--------------------------------------------------------------------------------------+-------+
| 10 | {"userId":"550","start":"2020-01-10 14:07:29","end":"2020-01-10 14:07:34","count":4} |   123 |  
+----+--------------------------------------------------------------------------------------+-------+

我想做的是从这个字符串中提取start日期，仅在另一列获得确切值的地方，并按提取的日期描述对其进行排序。

在第一步中，我已经尝试从字符串中提取olny日期，但它不起作用。代码如下：

SELECT REGEXP_SUBSTR(info, '[0-9]{4}-[0-9]{2}-[0-9]{1,2} [0-9]{2}:[0-9]{2}:[0-9]{2}') "REGEXPR_SUBSTR" from my_table WHERE value = 123;

问题是这个正则表达式正在工作，直到我里面没有减号。我的意思是 - 正则表达式[0-9]{4}正常工作 - 但如果我添加减号而不是它不是。

我有甲骨文 11g。

Answer 1

使用正则表达式[{,]s*"start"s*:s*"((\"|[^"])*)"查找具有键start和引号值的键值对，并提取第一个捕获组以查找值。

这将为给定键找到正确的值，无论 JSON 的顺序是否更改。

甲骨文设置：

CREATE TABLE test_data ( id, info, value ) AS  
SELECT 10, '{"userId":"550","start":"2020-01-10 14:07:29","end":"2020-01-10 14:07:34","count":4}', 123 FROM DUAL UNION ALL
SELECT 20, '{"count":1,"end":"2020-02-03 12:34:56","userId":"551","start":"2020-01-02 01:23:45"}', 456 FROM DUAL UNION ALL
SELECT 30, '{"userId":"552","otherData":""start":"1970-01-01 00:00:00"","start":"1999-01-01 00:00:00","end":"2000-01-01 23:59:59","count":42}', 789 FROM DUAL

查询：

SELECT id,
value,
REGEXP_SUBSTR( info, '[{,]s*"userId"s*:s*"((\"|[^"])*)"', 1, 1, NULL, 1 )
AS userID,
TO_DATE(
REGEXP_SUBSTR( info, '[{,]s*"start"s*:s*"((\"|[^"])*)"', 1, 1, NULL, 1 ),
'YYYY-MM-DD HH24:MI:SS'
) AS start_dt,
TO_DATE(
REGEXP_SUBSTR( info, '[{,]s*"end"s*:s*"((\"|[^"])*)"', 1, 1, NULL, 1 ),
'YYYY-MM-DD HH24:MI:SS'
) AS end_dt,
REGEXP_SUBSTR( info, '[{,]s*"count"s*:s*(d+)', 1, 1, NULL, 1 )
AS cnt
FROM   test_data;

输出：

身份证 |价值 |用户标识 |START_DT |END_DT |碳纳米管 -: |----: |:----- |:------------------ |:------------------ |:-- 10 |  123 |550 |2020-01-10 14：07：29 |2020-01-10 14：07：34 |4  20 |  456 |551 |2020-01-02 01：23：45 |2020-02-03 12：34：56 |1  30 |  789 |552 |1999-01-01 00：00：00 |2000-01-01 23：59：59 |42

db<>在这里小提琴

Answer 2

我不确定你的正则表达式有什么样的问题。

但也许你应该尝试一些更具可读性的东西。

SELECT regexp_replace('{"userId":"550","start":"2020-01-10 14:07:29","end":"2020-01-10 14:07:34","duration":4,"batchTotal":1}', '{"userId":"(.*?)","start":"(.*?)","end":"(.*?)","duration":([0-9]+),"batchTotal":([0-9]+)}','2') from dual

此外，您可以提取信息的不同部分，以regexp_reaplce更改数字\1，\2，\3，\4，\5

Answer 3

SELECT REGEXP_REPLACE(info, 
'.*start":"(.*)","end".*',
'1') "REGEXPR_SUBSTR" 
FROM YOURTABLE
WHERE CONDITIONS;

如果您不介意日期格式没有特定的正则表达式，并且您确定start和end关键字之间没有其他信息，则可能对您有用。

注意，这是一个贪婪的正则表达式，您可能需要调整它。