oracle 如何从VARCHAR2列中检索DATE值,VARCHAR2列是数字、字符、小数和日期的组合

idv4meu8  于 2023-08-04  发布在  Oracle
关注(0)|答案(2)|浏览(96)

如何从VARCHAR 2列中仅检索DATE值(MM/DD/YYYY)。VARCHAR 2列是数字、字符、小数和日期的组合。
Varchar 2列值如下所示:

Unity rrr
Project
12/31/2019
actyu
12/31/2018
12/31/2017
109.83
12/31/2016
2021
1111

字符串
我已经尝试了SUBSTR,REGEXP_REPLACE,但没有运气。

hfwmuf9z

hfwmuf9z1#

看看这样的东西是否有帮助。
样本数据:

SQL> WITH
  2     test (col)
  3     AS
  4        (SELECT 'Unity rrr' FROM DUAL
  5         UNION ALL
  6         SELECT 'Project' FROM DUAL
  7         UNION ALL
  8         SELECT '12/31/2019' FROM DUAL
  9         UNION ALL
 10         SELECT 'actyu' FROM DUAL
 11         UNION ALL
 12         SELECT '12/31/2018' FROM DUAL
 13         UNION ALL
 14         SELECT '12/31/2017' FROM DUAL
 15         UNION ALL
 16         SELECT '109.83' FROM DUAL
 17         UNION ALL
 18         SELECT '12/31/2016' FROM DUAL
 19         UNION ALL
 20         SELECT '2021' FROM DUAL
 21         UNION ALL
 22         SELECT '1111' FROM DUAL)

字符串
TO_DATE将返回

  • SYSDATE,如果出现错误
  • DATE数据类型值(如果COL是有效的日期值)

对于COL包含由斜杠分隔的[2位数+ 2位数+ 4位数]的所有行。

23  SELECT col, TO_DATE (col DEFAULT SYSDATE ON CONVERSION ERROR, 'mm/dd/yyyy') result
 24    FROM test
 25   WHERE REGEXP_LIKE (col, '^\d{2}/\d{2}/\d{4}$');

COL        RESULT
---------- ----------
12/31/2019 12/31/2019
12/31/2018 12/31/2018
12/31/2017 12/31/2017
12/31/2016 12/31/2016

SQL>

bmvo0sr5

bmvo0sr52#

在Oracle 12中,要转换字符串,您可以使用TO_DATE(value DEFAULT NULL ON CONVERSION ERROR, format_model)和格式模型FXMM/DD/YYYY(如果您不使用FX模型进行精确匹配,则可能会发现意外的匹配,请参阅下面示例输出的最后一行)。

SELECT value,
       TO_DATE(
         value DEFAULT NULL ON CONVERSION ERROR,
         'FXMM/DD/YYYY'
       ) As dt,
       TO_DATE(
         value DEFAULT NULL ON CONVERSION ERROR,
         'MM/DD/YYYY'
       ) As non_exact_format
FROM   table_name;

字符串
其中,对于样本数据:

CREATE TABLE table_name (value) AS
SELECT 'Unity rrr' FROM DUAL UNION ALL
SELECT 'Project' FROM DUAL UNION ALL
SELECT '12/31/2019' FROM DUAL UNION ALL
SELECT 'actyu' FROM DUAL UNION ALL
SELECT '12/31/2018' FROM DUAL UNION ALL
SELECT '12/31/2017' FROM DUAL UNION ALL
SELECT '109.83' FROM DUAL UNION ALL
SELECT '12/31/2016' FROM DUAL UNION ALL
SELECT '2021' FROM DUAL UNION ALL
SELECT '02/29/2001' FROM DUAL UNION ALL
SELECT '99/99/9999' FROM DUAL UNION ALL
SELECT '00/00/0000' FROM DUAL UNION ALL
SELECT '11111111' FROM DUAL;


输出:
| DT|非精确格式| NON_EXACT_FORMAT |
| --|--| ------------ |
| * 空 | 空 *| null |
| * 空 | 空 *| null |
| 2019-12-31 00:00:00| 2019-12-31 00:00:00| 2019-12-31 00:00:00 |
| * 空 | 空 *| null |
| 2018-12-31 00:00:00| 2018-12-31 00:00:00| 2018-12-31 00:00:00 |
| 2017-12-31 00:00:00| 2017-12-31 00:00:00| 2017-12-31 00:00:00 |
| * 空 | 空 *| null |
| 2016-12-31 00:00:00| 2016-12-31 00:00:00| 2016-12-31 00:00:00 |
| * 空 | 空 *| null |
| * 空 | 空 *| null |
| * 空 | 空 *| null |
| * 空 | 空 *| null |
| * 空 *| 1111-11-11 00:00:00| 1111-11-11 00:00:00 |
如果你只想要有效的日期,那么你也可以使用VALIDATE_CONVERSION过滤行:

SELECT value,
       TO_DATE(
         value DEFAULT NULL ON CONVERSION ERROR,
         'FXMM/DD/YYYY'
       ) As dt
FROM   table_name
WHERE  VALIDATE_CONVERSION(value AS DATE, 'FXMM/DD/YYYY') = 1;


对于样本数据,输出:
| DT| DT |
| --| ------------ |
| 2019-12-31 00:00:00| 2019-12-31 00:00:00 |
| 2018-12-31 00:00:00| 2018-12-31 00:00:00 |
| 2017-12-31 00:00:00| 2017-12-31 00:00:00 |
| 2016-12-31 00:00:00| 2016-12-31 00:00:00 |
fiddle

相关问题