--Specific Informatica PowerCenter Qs--
I have incoming data field like this and need to extract the substrings from either side of the hyphens and store them in individual fields of the target table. I am getting the correct results from the database but the same is not working in Informatica. In Expression my code says parsed successfully but nothing is getting loaded.
It would be great if someone can assist me with all the 8 REGEXP code lines as it seems it differs quite a bit as I traverse deep into the string.
select replace(regexp_substr('ABC-10000-DEF-200-*-*-XYZ-*' ,'[^-]*(-|$)',1,1), '-', '' ) from dual;
select replace(regexp_substr('ABC-10000-DEF-200-*-*-XYZ-*' ,'[^-]*(-|$)',1,2), '-', '' ) from dual;
select replace(regexp_substr('ABC-10000-DEF-200-*-*-XYZ-*' ,'[^-]*(-|$)',1,3), '-', '' ) from dual;
select regexp_substr('ABC-10000-DEF-200-*-*-XYZ-*','[^-]+',1,1) from dual;
select regexp_substr('ABC-10000-DEF-200-*-*-XYZ-*','[^-]+',1,2) from dual;
select regexp_substr('ABC-10000-DEF-200-*-*-XYZ-*','[^-]+',1,3) from dual;
INFA Case 1: When I am using the below, its succeeding for the first occurrence but coming as nulls for the other 7 substring extracts.
REG_EXTRACT(String_Input,'([^-]*),?([^-]*),?([^-]*).*',1) --> Succeeds
REG_EXTRACT(String_Input,'([^-]*),?([^-]*),?([^-]*).*',2) --> Null
REG_EXTRACT(String_Input,'([^-]*),?([^-]*),?([^-]*).*',3) -- Null and so on till 8.
Case 2: When I use the below, I get all Nulls.
REG_EXTRACT('String_Input','[^-]+',1,1) --> Null
REG_EXTRACT('String_Input','[^-]+',1,2) --> Null
REG_EXTRACT('String_Input','[^-]+',1,3) --> Null ```
