Instr function assistance

Question

I have built logic to fetch the data using 2 fields and using instr and I need to search for exact pattern matching in the below. However I am getting different pattern match output as well. Can anyone pls help on this.Also I wanted to use instr only not any regular match functions like regex_inst:

**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	test_INC.MC_INS_SO_STAT
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.

Answer 1

It is not quite clear what is the logic behind your expected output from sample data provided. If it is that you want to fetch the row where ATTR_VALUE contains OBJECT_NAME followed by semicolon and single quote with some more text after that then it could be done like below:

WITH    --  S a m p l e    D a t a :
    tbl (ATTR_VALUE, OBJECT_NAME) AS
        ( Select 'select ''exec test_INC.MC_INS_SO_STAT;'' FROM DEFAULTDB', 'test_INC.MC_INS_SO_STAT' From Dual Union All 
          Select 'select ''exec test_INC.MC_INS_SO_STAT_Anom''', 'test_INC.MC_INS_SO_STAT' From Dual Union All 
          Select 'select ''exec test_INC.MC_INS_SO_ST;''', Null  From Dual
        )

--    S Q L :
Select ATTR_VALUE
From   tbl
Where  InStr(ATTR_VALUE, Nvl(OBJECT_NAME, '*****') || ';''') > 0 And 
       Length( ATTR_VALUE) > 
       Length(SubStr( ATTR_VALUE, 1, InStr(ATTR_VALUE, OBJECT_NAME || ';''') -1) || OBJECT_NAME || ';''')

/*       R e s u l t :
ATTR_VALUE
--------------------------------------------------------
select 'exec test_INC.MC_INS_SO_STAT;' FROM DEFAULTDB       */

... If it is just about containing OBJECT_NAME followed by semicolon and sigle quote then you can get the same row with just the first WHERE clause condition:

...
Where  InStr(ATTR_VALUE, Nvl(OBJECT_NAME, '*****') || ';''') > 0