How to search particular value from column-CodePudding

i have a number of advance_criteria. inserted all select query in advance_criteria column. i am search for only company code is 17,41 from advance_criteria column. For example when i am trying below query executed.

    with Criteria as 
    (
    select 'SELECT ''Y'' FROM VW_MIS_QUOTE_POLICIES WHERE 
                ENTITY_REFERENCE = P_ENTITY_REFERENCE and COMPANY_CODE IN (17,41)' Advance_Criteria , '1'Col_Id from dual
                
     union all
    select 'select * FROM VW_MIS_QUOTE_POLICIES QP where QP.COMPANY_CODE       IN (''01'')' Advance_Criteria , '2'Col_Id from dual
    union all
    select 'SELECT ''Y'' FROM VW_MIS_QUOTE_POLICIES WHERE 
                ENTITY_REFERENCE = P_ENTITY_REFERENCE and COMPANY_CODE IN (''17'',''41'')' Advance_Criteria , '3'Col_Id from dual
    union all
    select '
V_RETURN := K_DOCUMENT_TEMPLATE_MANAGEMENT.F_CHECK_FORM_ON_ENDT(P_ENTITY_REFERENCE,P_ENTITY_TYPE, ''17399 0741'');

select * FROM VW_MIS_QUOTE_POLICIES QP where QP.COMPANY_CODE       IN (01, 02, 06, 41, 17)' Advance_Criteria , '4'Col_Id from dual
    union all
    select 'select DECODE (COUNT(1),0,1,0)
        INTO V_RETURN
        FROM VW_MIS_QUOTE_POLICIES QP,
        where QP.COMPANY_CODE IN (''01'',''02'',''06'',''17'',''41'')
        and QP.PRIMARY_STATE_CODE = ''WA''' Advance_Criteria , '5'Col_Id from dual
    union all
    select 'select 1 from vw_mis_quote_policies 
           WHERE  PROGRAM_CODE IN ( ''29'',''40'', ''46'', ''47'', ''48'')
          AND COMPANY_CODE IN (''01'',''02'',''06'',''09'',''29'')' Advance_Criteria , '6'Col_Id from dual
    union all
    select 'select * FROM VW_MIS_QUOTE_POLICIES QP where QP.COMPANY_CODE       IN (01, 02, 06)' Advance_Criteria , '7'Col_Id from dual
    )
    select * from Criteria where upper(Advance_Criteria) like upper('%COMPANY_CODE IN (17,41)%') 
    or
    Advance_Criteria like upper('%company_code in (''17'',''41'')%');

I showed the following output after executing the query.

ADVANCE_CRITERIA                                                                 COL_ID
--------------------------------------------------------------------------------------------
"SELECT 'Y' FROM VW_MIS_QUOTE_POLICIES WHERE 
ENTITY_REFERENCE = P_ENTITY_REFERENCE and COMPANY_CODE IN (17,41)"                  1
"SELECT 'Y' FROM VW_MIS_QUOTE_POLICIES WHERE 
ENTITY_REFERENCE = P_ENTITY_REFERENCE and COMPANY_CODE IN ('17','41')"              3

The expectation result

in Advance_Criteria column the search COMPANY_CODE is 17 and 41 like col_id 1,3,4,5,6

CodePudding user response：

You can use:

select *
from   Criteria
where  REGEXP_LIKE(
         Advance_Criteria,
         'COMPANY_CODE\s IN\s \((\s*'
           || '(\d \s*,\s*)*?17\s*,\s*(\d \s*,\s*)*?41(\s*,\s*\d )*|'
           || '(\d \s*,\s*)*?41\s*,\s*(\d \s*,\s*)*?17(\s*,\s*\d )*|'
           || q'[('\d '\s*,\s*)*'17'\s*,\s*('\d '\s*,\s*)*'41'(\s*,\s*'\d ')*|]'
           || q'[('\d '\s*,\s*)*'41'\s*,\s*('\d '\s*,\s*)*'17'(\s*,\s*'\d ')*]'
           || '\s*)\)',
         'i'
       );

Which, for the sample data, outputs:

ADVANCE_CRITERIA COL_ID

SELECT 'Y' FROM VW_MIS_QUOTE_POLICIES WHERE
ENTITY_REFERENCE = P_ENTITY_REFERENCE and COMPANY_CODE IN (17,41) 1

SELECT 'Y' FROM VW_MIS_QUOTE_POLICIES WHERE
ENTITY_REFERENCE = P_ENTITY_REFERENCE and COMPANY_CODE IN ('17','41') 3

select * FROM VW_MIS_QUOTE_POLICIES QP where QP.COMPANY_CODE IN (01, 02, 06, 41, 17) 4

select DECODE (COUNT(1),0,1,0)
INTO V_RETURN
FROM VW_MIS_QUOTE_POLICIES QP,
where QP.COMPANY_CODE IN ('01','02','06','17','41')
and QP.PRIMARY_STATE_CODE = 'WA' 5

ADVANCE_CRITERIA	COL_ID
SELECT 'Y' FROM VW_MIS_QUOTE_POLICIES WHERE ENTITY_REFERENCE = P_ENTITY_REFERENCE and COMPANY_CODE IN (17,41)	1
SELECT 'Y' FROM VW_MIS_QUOTE_POLICIES WHERE ENTITY_REFERENCE = P_ENTITY_REFERENCE and COMPANY_CODE IN ('17','41')	3
select * FROM VW_MIS_QUOTE_POLICIES QP where QP.COMPANY_CODE IN (01, 02, 06, 41, 17)	4
select DECODE (COUNT(1),0,1,0) INTO V_RETURN FROM VW_MIS_QUOTE_POLICIES QP, where QP.COMPANY_CODE IN ('01','02','06','17','41') and QP.PRIMARY_STATE_CODE = 'WA'	5

Note: #6 does not match as it does not contain 17 or 41.

db<>fiddle here