Oracle REGEX_SUBSTR不遵守空值 [英] Oracle REGEX_SUBSTR Not Honoring null values

问题描述

我遇到了regex_substr不遵守null值的问题.

I have an issue of regex_substr not honoring the null value.

select
REGEXP_SUBSTR ('2035197553,2,S,14-JUN-14,,P', '[^,]+', 1, 1)    AS phn_nbr,
REGEXP_SUBSTR ('2035197553,2,S,14-JUN-14,,P', '[^,]+', 1, 2)    AS phn_pos,
REGEXP_SUBSTR ('2035197553,2,S,14-JUN-14,,P', '[^,]+', 1, 3)    AS phn_typ,
REGEXP_SUBSTR ('2035197553,2,S,14-JUN-14,,P', '[^,]+', 1, 4)    AS phn_strt_dt,
REGEXP_SUBSTR ('2035197553,2,S,14-JUN-14,,P', '[^,]+', 1, 5)    AS phn_end_dt,
REGEXP_SUBSTR ('2035197553,2,S,14-JUN-14,,P', '[^,]+', 1, 6)    AS pub_indctr
from dual;

如果phn_end_dt为null，而pub_indctr不为null，则pub_indctr的值将移至phn_end_dt.

If the phn_end_dt is null and pub_indctr is not null, the values of pub_indctr are shifted to phn_end_dt.

结果:-

PHN_NBR    PHN_POS PHN_TYP PHN_STRT_DT PHN_END_DT PUB_INDCTR  
---------- ------- ------- ----------- ---------- ------------
2035197553 2       S       14-JUN-14   P 

应为

PHN_NBR    PHN_POS PHN_TYP PHN_STRT_DT PHN_END_DT PUB_INDCTR  
---------- ------- ------- ----------- ---------- ------------
2035197553 2       S       14-JUN-14               P 

有什么建议吗?

推荐答案

感谢您指出正确的方向，我已使用它来解决此问题.

Thanks for pointing me in the right direction, I have used this to solve the issue.

SELECT REGEXP_SUBSTR (val, '([^,]*),|$', 1, 1, NULL, 1) phn_nbr , REGEXP_SUBSTR (val, '([^,]*),|$', 1, 2, NULL, 1) phn_pos , REGEXP_SUBSTR (val, '([^,]*),|$', 1, 3, NULL, 1) phn_typ , REGEXP_SUBSTR (val, '([^,]*),|$', 1, 4, NULL, 1) phn_strt_dt , REGEXP_SUBSTR (val, '([^,]*),|$', 1, 5, NULL, 1) phn_end_dt , REGEXP_SUBSTR (val || ',', '([^,]*),|$', 1, 6, NULL, 1) pub_indctr FROM (SELECT '2035197553,2,S,14-JUN-14,,P' val FROM dual );

SELECT REGEXP_SUBSTR (val, '([^,]*),|$', 1, 1, NULL, 1) phn_nbr , REGEXP_SUBSTR (val, '([^,]*),|$', 1, 2, NULL, 1) phn_pos , REGEXP_SUBSTR (val, '([^,]*),|$', 1, 3, NULL, 1) phn_typ , REGEXP_SUBSTR (val, '([^,]*),|$', 1, 4, NULL, 1) phn_strt_dt , REGEXP_SUBSTR (val, '([^,]*),|$', 1, 5, NULL, 1) phn_end_dt , REGEXP_SUBSTR (val || ',', '([^,]*),|$', 1, 6, NULL, 1) pub_indctr FROM (SELECT '2035197553,2,S,14-JUN-14,,P' val FROM dual );

Oracle版本:-Oracle Database 11g企业版11.2.0.4.0版-64位生产

Oracle Version:- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

这篇关于Oracle REGEX_SUBSTR不遵守空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！