REG_EXTRACT doesnt work in IICS Informatica

Question

I have a value (URL)

https://test.com/insight/api/view?entity_type=event&entity_id=6557856

and when I am trying to apply this function:

REG_EXTRACT (value, 'entity_id=(\d+)',1)

i am getting NULL instead of a proper value. https://regex101.com/ validates the pattern and it seems to be correct.

Answer 1

It isn't enough to just match the pattern you want to extract; you have to match the entire string and then have groupings for the bits you want to extract.

Even though this question is for IICS, if we look closely at the (poorly explained) example provided for Power Centre, we can see that the reg-ex they've provided matches the entire example string but breaks it down into 3 groups (the bracketed sections) and from those it takes the 2nd grouping:

REG_EXTRACT( Employee_Name, '(\w+)\s+(\w+)\s+(\w+)', 2)

Employee_Name	Return Value
Stephen Graham Smith	Graham
Juan Carlos Fernando	Carlos

Although I've not tested it, I'm fairly certain in the above that if someone came through with 4 names, (e.g. Stephen Graham Smith Johnstone), then their pattern wouldn't match either.

The way I'd approach your string in RegEx101 is first match everything and then narrow it down to get the grouping in place:

https://test.com/insight/api/view?entity_type=event&entity_id=6557856

• .* to match the entire URL
• &entity_id= to narrow down where the grouping will be
• (\d+) to create a group around the digits

Giving:

.*&entity_id=(\d+)

Obviously you might want to make it a bit stricter, but that should be enough to get you the match you are looking for.

Answer 2

This is not how REG_EXTRACT works. It is not similar to RegEx101. Have a look at this answer - you first need to write a matcher that will cover whole string and then extract the required group.