将Oracle VARCHAR2转换为DATE并排除无效数据 [英] Convert Oracle VARCHAR2 to DATE and exclude invalid data

问题描述

我在列column_name VARCHAR2中包含各种数据.为了将其转换为DATE数据类型，我编写了以下表达式:

I have column column_name VARCHAR2 with various data. To convert it to DATE datatype I wrote following expression:

SELECT TO_DATE(column_name, 'YYYY/MM/DD') FROM schema.table;

但这给了我错误:

ORA-01841 :(完整)年份必须在-4713和+9999之间，并且不能为0 01841. 00000-(完整)年份必须在-4713到+9999之间，并且不能为0"

ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"

*原因:输入的年份不合法

*Cause: Illegal year entered

*操作:输入指定范围内的年份

*Action: Input year in the specified range

列由NULL，space，00000000和字符串日期(例如"20161111")组成.

Column consist of NULL, space, 00000000 and string dates like "20161111".

为排除无效数据，我决定使用DECODE:

To exclude invalid data I decided to use DECODE:

SELECT DECODE(column_name, 
              '', NULL, 
              '00000000', NULL, 
              TO_DATE(column_name, 'YYYY/MM/DD')) 
FROM schema.table; 

但是在这种情况下，我会遇到以下错误:

But in this case I get a following error:

ORA-01841 :(完整)年份必须在-4713和+9999之间，并且不能为0 01841. 00000-(完整)年份必须在-4713到+9999之间，并且不能为0"

ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"

*原因:输入的年份不合法

*Cause: Illegal year entered

*操作:输入指定范围内的年份

*Action: Input year in the specified range

数据示例:

| # | column_name |
|---|-------------|
| 1 | 00000000    |
| 2 |             |
| 3 | (null)      |
| 4 | 20161111    |

我在做什么错了?

是否有解决方案可以排除所有无效数据，而在DECODE情况下不包括这些无效数据?

Is there any solution to exclude all invalid data without including it in cases of DECODE?

推荐答案

您可以编写如下函数:

CREATE OR REPLACE FUNCTION VARCHAR_TO_DATE(str IN VARCHAR2) RETURN DATE AS
BEGIN
   RETURN TO_DATE(str, 'YYYY/MM/DD');
EXCEPTION
   WHEN OTHERS THEN
      RETURN NULL;
END;

然后将其用作

SELECT varchar_to_date(column_name) FROM schema.table;

任何无效的字符串都将导致NULL值.

Any invalid strings will result in a NULL value.

这篇关于将Oracle VARCHAR2转换为DATE并排除无效数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！