We have a business scenario where the Source Data is getting populated as 'FIRST FINANCIAL SECURITIES OF AMERICA I' where 'I' is getting populated as suffix. We need to remove the character 'I' from suffix and derive the data as 'FIRST FINANCIAL SECURITIES OF AMERICA' but when we use the below code in the expression transformation it is removing the character 'I' for all occurrence and gives the following out put string 'FRST FNANCAL SECURTES OF AMERCA'.
How can I achieve the above logic using REG_MATCH and REPLACESTR ?
**--parse suffix txt from last name**
IIF(REG_MATCH(LAST_NAME,'.*\sI\s.*') OR REG_MATCH(LAST_NAME,'.*\sI'),REPLACESTR(1,LAST_NAME,'I',''),
IIF(REG_MATCH(LAST_NAME,'.*\sII\s.*') OR REG_MATCH (LAST_NAME,'.*\sII'),REPLACESTR(1,LAST_NAME,'II',''),
IIF(REG_MATCH(LAST_NAME,'.*\sIII\s.*') OR REG_MATCH(LAST_NAME,'.*\sIII'),REPLACESTR(1,LAST_NAME,'III',''),
IIF(REG_MATCH(LAST_NAME,'.*\sIV\s.*') OR REG_MATCH(LAST_NAME,'.*\sIV'),REPLACESTR(1,LAST_NAME,'IV',''),
IIF(REG_MATCH(LAST_NAME,'.*\sV\s.*') OR REG_MATCH(LAST_NAME,'.*\sV'),REPLACESTR(1,LAST_NAME,'V',''),
IIF(REG_MATCH(LAST_NAME,'.*\sJR\s.*') OR REG_MATCH(LAST_NAME,'.*\sJR'),REPLACESTR(1,LAST_NAME,'JR',''),
IIF(REG_MATCH(LAST_NAME,'.*\sJR.\s.*') OR REG_MATCH(LAST_NAME,'.*\sJR.'),REPLACESTR(1,LAST_NAME,'JR.',''),
IIF(REG_MATCH(LAST_NAME,'.*\sSR\s.*') OR REG_MATCH(LAST_NAME,'.*\sSR'),REPLACESTR(1,LAST_NAME,'SR',''),
IIF(REG_MATCH(LAST_NAME,'.*\sSR.\s.*') OR REG_MATCH(LAST_NAME,'.*\sSR.'),REPLACESTR(1,LAST_NAME,'SR.',''),
IIF(REG_MATCH(LAST_NAME,'.*\s3RD\s.*') OR REG_MATCH(LAST_NAME,'.*\s3RD'),REPLACESTR(1,LAST_NAME,'3RD',''),
LAST_NAME))))))))))
Did you try something like:
IIF(REG_MATCH(LAST_NAME, '.*\\sI$'), <- NOTE: no trailing asterix
REG_REPLACE(LAST_NAME, '\\sI$', ''),
SOURCE_COLUMN)
Your first REG_MATCH returns true if there is an I surrounded by spaces anywhere in the LAST_NAME. I don't think this is desired.
Then you're using REPLACESTR where I guess REG_REPLACE should be used. Review and let me know in case of further quesitons.