如何从字符串oracle中提取日期格式 [英] how to extract date format from a string oracle
本文介绍了如何从字符串oracle中提取日期格式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图从数据格式为
CI+OT+1x78xx+POL.SLATINA+29-08-2003+14-07-2013
或
BI+GV+591xxx+MUN CAMPIA TURZII+03-02-1997+
因此它被定界了+五次(其中一些永远不会过期)
so it's delimited by + five times (some of them does not expire ever)
我已经做到了,但是没用...
I've done this, but it don't works...
select
set_act_id_1,
to_number(sysdate-to_date(substr(set_act_id_1,instr(set_act_id_1,'+',1,5)+1,length(set_act_id_1)),'DD-MM-YYYY'))
from STAGE.CUSTOMERS
where set_act_id_1 is not null
group by set_act_id_1
having sysdate-to_date(substr(set_act_id_1,instr(set_act_id_1,'+',1,5)+1,length(set_act_id_1)),'DD-MM-YYYY')<0;
推荐答案
SELECT set_act_id_1,
TO_DATE( REGEXP_SUBSTR( set_act_id_1, '[^+]+', 1, 6 ), 'DD-MM-YYYY' )
FROM STAGE.CUSTOMERS
WHERE set_act_id_1 is not null
或
SELECT set_act_id_1,
TO_DATE( SUBSTR( set_act_id_1, INSTR( set_act_id_1, '+', 1, 5 ) + 1 ), 'DD-MM-YYYY' )
FROM STAGE.CUSTOMERS
WHERE set_act_id_1 is not null
更新:
SELECT CASE
WHEN REGEXP_LIKE( expiry_date, '^\d{1,2}[ \/-](JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)[ \/-]\d{4}$', 'i' )
THEN TO_DATE( expiry_date, 'dd mon yyyy' )
WHEN REGEXP_LIKE( expiry_date, '^\d{1,2}[ \/-](JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)[ \/-]\d{2}$', 'i' )
THEN TO_DATE( expiry_date, 'dd mon yy' )
WHEN REGEXP_LIKE( expiry_date, '^(0?[1-9]|[12]\d|3[01])[ \/-](0?[1-9]|1[0-2])[ \/-]\d{4}$' )
THEN TO_DATE( expiry_date, 'dd mm yyyy' )
WHEN REGEXP_LIKE( expiry_date, '^(0?[1-9]|1[0-2])[ \/-](0?[1-9]|[12]\d|3[01])[ \/-]\d{4}$' )
THEN TO_DATE( expiry_date, 'mm dd yyyy' )
WHEN REGEXP_LIKE( expiry_date, '^(0?[1-9]|[12]\d|3[01])[ \/-](0?[1-9]|1[0-2])[ \/-]\d{2}$' )
THEN TO_DATE( expiry_date, 'dd mm yy' )
WHEN REGEXP_LIKE( expiry_date, '^(0?[1-9]|1[0-2])[ \/-](0?[1-9]|[12]\d|3[01])[ \/-]\d{2}$' )
THEN TO_DATE( expiry_date, 'mm dd yy' )
ELSE NULL
END AS expiry_date
FROM (
SELECT set_act_id_1,
SUBSTR( set_act_id_1, INSTR( set_act_id_1, '+', 1, 5 ) + 1 ) As expiry_date
FROM STAGE.CUSTOMERS
WHERE set_act_id_1 is not null
)
这篇关于如何从字符串oracle中提取日期格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文