oracle-databaseinformaticainformatica-powercenterinformatica-powerexchange

Instr to find the exact pattern match in informatica

Instr function logic:

**Input** : attr_value and object_name are the field names.
attr_value object_name
select 'exec test_INC.MC_INS_SO_STAT;' FROM DEFAULTDB test_INC.MC_INS_SO_STAT
select 'exec test_INC.MC_INS_SO_STAT_Anom' abc_test
select 'exec test_INC.MC_INS_SO_ST;' 
**Output** :
attr_value
select 'exec test_INC.MC_INS_SO_STAT;' FROM DEFAULTDB
select 'exec test_INC.MC_INS_SO_STAT_Anom' 
**Expected Output**:
attr_value
select 'exec test_INC.MC_INS_SO_STAT;' FROM DEFAULTDB

I have tried this logic: IIF (INSTR (upper(attr_value),upper(OBJECT_NAME)) > 0 ,1, 0 ) but its giving me different pattern too as mentioned in output which is not as per my expected output. If you can pls help how to tweak the logic to fetch the exact match records only.

Solution

  • This one is tricky. The logic you use is correct, but you need to look for the attribute with a possible ending char, not just the value itself. Let me try to explain by giving an example:

    The function:

    IIF (INSTR (upper(attr_value),upper(OBJECT_NAME) || CHR(39)) > 0, 1, 0 ) 
OR
IIF (INSTR (upper(attr_value),upper(OBJECT_NAME) || ';') > 0, 1, 0 )

    In other words I'd be searching for test_INC.MC_INS_SO_STAT' (including single closing quote) or test_INC.MC_INS_SO_STAT; (including semicolon).