问题描述
我在列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;
但这给了我错误:
*原因:输入的年份不合法
*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:
*原因:输入的年份不合法
*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并排除无效数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!