This post will help you to understand how to match a regular expression pattern in Decode function in informatica PowerCenter.

If the requirement is to search for a pattern and assign the values accordingly, you can configure your DECODE function along with REG_MATCH to match with a pattern.

In my case if any plan status starts with OPEN, I should change that to OPEN. Same case with CLOSED*. You can use REG_MATCH to search for the specific pattern and then use DECODE to assign the new value as shown below.

Have any questions? or need help with this topic, please do reach out to me at kvtinformatica@gmail.com. Don’t forget to follow my Youtube channel and FB page, Lets stay connected and learn together.