Extract vales from a column

this should work:

with query_1 as (SELECT
AAA.col_1
,REGEXP_SUBSTR(aaa.col_2,'[^,]+', 1, LEVEL) AS KEYWORD
FROM aaa
CONNECT BY
level <= REGEXP_count(aaa.col_2,',') + 1
and prior aaa.col_1 = aaa.col_1
and prior sys_guid() is not null)
Select * from XYZ where (Select Col_1 From query_1 Where keyword IN ('1960', '1961'))

Select allOpen in new window

>>Select * from XYZ where (Select Col_1 From AAA Where Col_2 IN ('1960', '1961');<<
That syntax will not work. You would have to say "where exists ..." or "where some column in ..." Perhaps you can provide sample data for both tables AAA and XYZ with your expected output and the criteria used for it.

Hi Walter, I posted my reply but it did not up load. I will have to do it chunks.

Here is the part of the procedure where I want to implement your solotion.  

SELECT
          c1_rec.Srch_Trm, N.SBJT_ID, D.FILG_DT, D.LST_UPD_TS, N.SBJT_ID,
          upper(F.LGL_RAW_PRTY_FULL_NM), c1_rec.Srch_Date, c1_rec.Srch_Term_Id, c1_rec.Concept_Grp, c1_rec.Birth_Year, c1_rec.Srch_Typ, c1_rec.Rslvd_Grp_Nm    
          BULK COLLECT INTO  l_sSrch_Trm, l_sDoc_Id, l_sFilg_Dt, l_sLST_UPD_TS, l_sSBJT_ID, l_sLGL_RAW_PRTY_FULL_NM, l_sSrch_Date, l_sSrch_Term_Id,
          l_sConcept_Grp, l_sBirth_Year, l_sSrch_Typ, l_sRslvd_Grp_Nm        
          FROM BASE.SBJT N,  BASE.FILG_NSTUTN F, BASE.DOC D
          WHERE CONTAINS (N.SBJT_RAW_PRTY_FULL_NM, C1_REC.Srch_Trm)>0
          AND N.SBJT_ID=F.DOC_ID
          AND N.SBJT_ID=D.DOC_ID
          AND To_Char(N.NDVDL_BIRTH_YEAR) IN C1_REC.Birth_Year   --- This is where the RegExp portin will go
          AND D.LST_UPD_TS > C1_REC.Srch_Date;  

-- More to follow

The matching (Source) table  has values like this.

BIRTH_YEAR
---------------
1947,1952
1947,1952
1947,1952
1947,1952
1947,1952
1947,1952

Table against which the vales are to be matched are:
SRCH_TERM_ID            BIRTH_YEAR
------------                      ------------------
          11                       1947
           11                     1952
          12                      1947
           12                     1952
          18                       1947
           18                      1952
    etc....

if want to see the entire procedure, here it is:

CREATE OR REPLACE PROCEDURE Sars_Of_Int_SBBY
 IS
  c_row_processed    CONSTANT number(4) := 1000;
  c_row_processed_2  CONSTANT number(4) := 1000;
  v_my_loops        pls_integer;
  v_my_loops_2      pls_integer;
  v_row_count       pls_integer;
  v_Srch_Obj_Nm    Varchar2(2000) := 'SBJT: ';
  v_Srch_Birth_Yr  Varchar2(2000) := 'BIRTH_YEAR: ';
  v_Error_Code     Number;
  v_Srch_Trm       Varchar2(2000);
  v_Srch_Date      Date;
  v_Srch_Term_Id   Integer;
  v_SBJT_ID        Number (38);
  v_Concept_Grp    Varchar2(500);
  v_Srch_Typ       Varchar2(128);
  v_Rslvd_Grp_Nm   Varchar2 (250);
  v_Error_Message  Varchar2(100);
  Type sSrch_Trm   IS TABLE OF       Varchar2 (2000);  
  Type sDoc_Id     IS TABLE OF       Number (12);
  Type sSBJT_ID    IS TABLE OF       Number (38);
  Type sSrch_Term_Id IS TABLE OF Integer;
  Type sSrch_Date IS TABLE OF    Date;
  Type sFilg_Dt IS Table OF      Date;
  Type sLST_UPD_TS IS Table OF   Date;
  Type sLGL_RAW_PRTY_FULL_NM is TABLE OF Varchar2 (1000);
  Type sConcept_Grp IS TABLE OF  Varchar2 (500);
  Type sBirth_Year IS TABLE OF   Varchar2 (1000);
  Type sSrch_Typ IS TABLE OF     Varchar2(128);
  Type sRslvd_Grp_Nm IS TABLE OF Varchar2 (250);
  l_sSrch_Trm              sSrch_Trm;
  l_sFilg_Dt               sFilg_Dt;
  l_sDOC_Id                sDoc_Id;
  l_sSrch_Date             sSrch_Date;
  l_sSrch_Term_Id          sSrch_Term_Id;
  l_sSBJT_ID               sSBJT_ID;
  l_sLST_UPD_TS            sLST_UPD_TS;
  l_sLGL_RAW_PRTY_FULL_NM  sLGL_RAW_PRTY_FULL_NM;
  l_sConcept_Grp           sConcept_Grp;
  l_sBirth_Year            sBirth_Year;
  l_sSrch_Typ              sSrch_Typ;
  l_sRslvd_Grp_Nm          sRslvd_Grp_Nm;
  e_no_records_found       Exception;
 
   BEGIN
--Populate Table WI_SO_SRCH_SBBY_TEMP  
   Execute Immediate ('Truncate Table WI_SO_SRCH_SBBY_TEMP');
          Insert Into WI_SO_SRCH_SBBY_TEMP (
               Select SRCH_TERM_ID, SRCH_STRNG_2, SRCH_FROM_INIT
               From WI_SRCH_TERM
               Where WI_SRCH_TERM.SRCH_TYP = 'SBJT_BY'
               MINUS
               Select SRCH_TERM_ID, BIRTH_YEAR, SRCH_FROM
               From WI_SO_SRCH_FROM_SBBY);
        Commit;
--Populate Table WI_SO_SRCH_FROM_SBBY          
       Insert Into WI_SO_SRCH_FROM_SBBY (
               Select SRCH_TERM_ID, sysdate, SRCH_STRNG_2, SRCH_FROM_INIT, Null, Null
               From WI_SRCH_TERM
               Where WI_SRCH_TERM.SRCH_TYP = 'SBJT_BY'
               MINUS
               Select SRCH_TERM_ID, sysdate, BIRTH_YEAR, SRCH_FROM, Null, Null
               From WI_SO_SRCH_FROM_SBBY);
        Commit;            
-- Populate Source Table WI_SO_SBBY          
     Execute Immediate ('Truncate Table WI_SO_SBBY');  
        Insert Into WI_SO_SBBY (SRCH_TRM, SRCH_DATE, SRCH_TERM_ID,  CONCEPT_GRP, BIRTH_YEAR, SRCH_TYP, RSLVD_GRP_NM, Run_Date)
        Select SRCH_STRNG_1, SRCH_FROM_INIT, SRCH_TERM_ID, CONCEPT_GRP, SRCH_STRNG_2, SRCH_TYP, RSLVD_GRP_NM, sysdate
        From WI_SRCH_TERM
        Where SRCH_TYP = 'SBJT_BY';
      Commit;          
-- Empty Destination Table WI_SO_SBBY_OUT  
     Execute Immediate ('Truncate Table WI_SO_SBBY_OUT');
     v_my_loops := 0;
   -- If the source table (WI_SO_SBBY) has no records, Raise exeception,
   -- Enter "No Record Found" in WI_SO_NO_REC  
        Select count (*) into v_row_count FROM WI_SO_SBBY ;
        If     v_row_count = 0 Then
           RAISE e_no_records_found;
   -- Create a cursor to hold values from table WI_SO_SBBY          
    Else
       DECLARE
         CURSOR c1_cur
    IS
       SELECT A.Srch_Trm, NVL (B.SRCH_UPTO, B.SRCH_FROM) Srch_Date, A.Srch_Term_Id, A.SBJT_ID, A.Concept_Grp, B.Birth_Year, A.Srch_typ, A.Rslvd_Grp_Nm
       INTO v_Srch_Trm, v_Srch_Date, v_Srch_Term_Id, v_SBJT_ID, v_Concept_Grp, v_Srch_Birth_Yr, v_Srch_typ, v_Rslvd_Grp_Nm
       FROM WI_SO_SBBY A, WI_SO_SRCH_FROM_SBBY B
       WHERE A.Srch_Term_Id = B.Srch_Term_Id;
     BEGIN
        For c1_rec IN c1_cur
    -- Loop through WI_SO_SBJT to find matching records in Tables
    -- BASE.SBJT, BASE.DOC And populate table WI_SO_SBBY_OUT
      LOOP
          BEGIN          
       SELECT
          c1_rec.Srch_Trm, N.SBJT_ID, D.FILG_DT, D.LST_UPD_TS, N.SBJT_ID,
          upper(F.LGL_RAW_PRTY_FULL_NM), c1_rec.Srch_Date, c1_rec.Srch_Term_Id, c1_rec.Concept_Grp, c1_rec.Birth_Year, c1_rec.Srch_Typ, c1_rec.Rslvd_Grp_Nm    
          BULK COLLECT INTO  l_sSrch_Trm, l_sDoc_Id, l_sFilg_Dt, l_sLST_UPD_TS, l_sSBJT_ID, l_sLGL_RAW_PRTY_FULL_NM, l_sSrch_Date, l_sSrch_Term_Id,
          l_sConcept_Grp, l_sBirth_Year, l_sSrch_Typ, l_sRslvd_Grp_Nm        
          FROM BASE.SBJT N,  BASE.FILG_NSTUTN F, BASE.DOC D
          WHERE CONTAINS (N.SBJT_RAW_PRTY_FULL_NM, C1_REC.Srch_Trm)>0
          AND N.SBJT_ID=F.DOC_ID
          AND N.SBJT_ID=D.DOC_ID
          AND To_Char(N.NDVDL_BIRTH_YEAR) IN C1_REC.Birth_Year
          AND D.LST_UPD_TS > C1_REC.Srch_Date;        
          FORALL i IN 1..l_sSrch_Trm.count
          INSERT INTO WI_SO_SBBY_OUT (SRCH_TRM, SRCH_DATE, SRCH_TERM_ID, SBJT_ID, CONCEPT_GRP, BIRTH_YEAR, SRCH_TYP, RSLVD_GRP_NM, DOC_ID, FILG_DT, LAST_UPDATE, LGL_RAW_PRTY_FULL_NM, TRIGGER_TXT, RUN_DATE)
          VALUES (l_sSrch_Trm(i), l_sSrch_Date(i), l_sSrch_Term_Id(i), l_sSBJT_ID(i),l_sConcept_Grp(i), l_sBirth_Year(i), l_sSrch_Typ(i), l_sRslvd_Grp_Nm(i), l_sDoc_Id(i), l_sFilg_Dt(i), l_sLST_UPD_TS(i), l_sLGL_RAW_PRTY_FULL_NM(i), v_Srch_Obj_Nm||l_sSrch_Trm(i)||v_Srch_Birth_Yr||l_sBirth_Year(i), sysdate);                  
      v_my_loops := v_my_loops +1;      
      If mod(c1_cur%rowcount, c_row_processed) =0 Then
         NULL;
      End If;
     
      EXCEPTION
             When Others then
             v_Error_Code := SQLCODE;
       v_Error_Message := Substr (SQLERRM, 1, 75);
       Insert Into WI_SO_ERRM (Search_Term, code, message, info, Record_Source, Record_Time)
         Values  (c1_rec.SRCH_TRM,v_Error_Code, v_Error_Message, 'Oracle error occured', 'Sars_Of_Int_SBJT', sysdate);        
           Commit;
        End;  
    End Loop;
    Commit;
   End;
  End If;
  -- Update Table WI_SO_SRCH_FROM_SBBY
  v_my_loops_2 := 0;
DECLARE
  v2_Srch_Term_Id          Integer;
  v2_Run_Begin             Date;
  v2_Birth_Year            Varchar2(1000 CHAR);
  v2_Srch_From             Date;
     CURSOR c2_cur
        IS
       SELECT SRCH_TERM_ID, RUN_BEGIN, Birth_Year, SRCH_FROM
       INTO v2_Srch_Term_Id, v2_Run_Begin, v2_Birth_Year, v2_Srch_From
       FROM WI_SO_SRCH_FROM_SBBY;
  Type sSrch_Trm_2 IS TABLE OF       Varchar2 (2000);
  Type sSrch_Term_Id_2 IS TABLE OF   Integer;
  Type sSrch_From_2 IS TABLE OF      Date;
  Type sSrch_Run_Begin_2 IS TABLE OF Date;
  Type slst_upd_ts_2 is TABLE OF     Date;
  Type sBirth_Yr_2 IS TABLE OF       Varchar2 (1000);
  l2_sSrch_Term_Id_2       sSrch_Term_Id_2;
  l2_sSrch_Run_Begin_2     sSrch_Run_Begin_2;
  l2_sSrch_From_2          sSrch_From_2;
  l2_slst_upd_ts_2         slst_upd_ts_2;
  l2_sSrch_Trm_2           sSrch_Trm_2;
  l2_sBirth_Yr_2           sBirth_Yr_2;  
    BEGIN
        For c2_rec IN c2_cur
      LOOP
        BEGIN
          SELECT c2_rec.SRCH_TERM_ID, c2_rec.Run_Begin, c2_rec.Birth_Year, c2_rec.SRCH_FROM, Max(E.LST_UPD_TS)
          BULK COLLECT INTO l2_sSrch_Term_Id_2, l2_sSrch_Run_Begin_2, l2_sBirth_Yr_2, l2_sSrch_From_2, l2_slst_upd_ts_2
          FROM WI_SO_SRCH_FROM_SBBY A, WI_SO_SBBY_OUT B, BASE.DOC E
          WHERE A.SRCH_TERM_ID = B.SRCH_TERM_ID
          AND   B.DOC_ID = E.DOC_ID;
          FORALL i IN 1.. l2_sSrch_Term_Id_2.count
          UPDATE WI_SO_SRCH_FROM_SBBY SET SRCH_UPTO = l2_slst_upd_ts_2(i),
                                          Run_End   = Sysdate;
        v_my_loops_2 := v_my_loops_2 +1;
             If mod(c2_cur%rowcount, c_row_processed_2) = 0 Then
                NULL;
             End If;
         End;
    End Loop;
        Commit;
    End;

EXCEPTION
    When e_no_records_found Then
        Insert Into WI_SO_NO_REC (out_Put_SARS, Record_Source, Record_Time) VALUES ('Input_Table has no records to process', 'Sars_Of_Int_SBJT',sysdate);
        Commit;
End Sars_Of_Int_SBBY;
/

Ok, so what you need to do is to apply my query on your source table, then you can compare with the table that have the search terms.
Please try that and let me know where you find any issues.

Looking into the code.

Just like your previous questions:  Small sample data and expected results help a lot.

You provided sample data for one table but not the other table and expected results.

Would a simple INSTR work?

where...
and instr(column_with_multiple_years,single_year_value) > 0
...

Looking into your code, I cannot identify which table or cursor will have the birth years in one row (1952,1960,1975) and the table that will have one per row. Could you please point that out?

Hi walter,

Thanks for the quick reply.

The cursor C1_CUR (B.Birth_Year) has the values, like this:

BIRTH_YEAR
 ---------------
 1947,1952
 1947,1952
 1947,1952
 1947,1952
 1947,1952
 1947,1952

The table against which the vales are to be matched (SBJT) have values like,

SRCH_TERM_ID            BIRTH_YEAR
 ------------                      ------------------
           11                       1947
            11                     1952
           12                      1947
            12                     1952
           18                       1947
            18                      1952
     etc....

I think regular expressions would be overkill here.  It is a very expensive function call to make.

Try INSTR:

...
AND INSTR(To_Char(N.NDVDL_BIRTH_YEAR),C1_REC.Birth_Year) > 0
...

I agree with slightvw. Considering how your code is structured, if you use INSTR that will suffice.
So, the following line:

AND To_Char(N.NDVDL_BIRTH_YEAR) IN C1_REC.Birth_Year

Select allOpen in new window

would be replaced by:

AND INSTR(To_Char(N.NDVDL_BIRTH_YEAR),C1_REC.Birth_Year) > 0

Select allOpen in new window

Experts, Thanks a lot. I will give it a try first thing tomorrow morning.

Hi experts due to lack of valid data on tables in question, I have not been able to test the modifications you suggested. I am in th eprocess of creating test data to ensure there are no hickups. I have a question. How will you describe the line below in plain english.

AND INSTR(To_Char(N.NDVDL_BIRTH_YEAR),C1_REC.Birth_Year) > 0

The INSTR funstion, looks for substring in a string and returns the nth occurance of the substring.

I have learned a lot for the Experts at Experts exchange and like to continue doing that.

>>How will you describe the line below in plain english.


I don't know that I can do any better than you already did.

It looks for the occurrence of one string in another string.  Which occurrence and starting position is optional and defaults to 1.

The online docs typically have good examples and explanations:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions080.htm#SQLRF00651

Create simple test cases to see things in action:
select instr('12345a7890a123','a') from dual;
select instr('12345a7890a123','a',1,2) from dual;

First one:
What position is the first 'a' in the string?
Next:
What position is the 'a' starting at position 1 and looking for the second occurrence.

Hi Experts,

I made the suggested change as below.

AND INSTR(To_Char(N.NDVDL_BIRTH_YEAR),C1_REC.Birth_Year) > 0

What I am seeing is that if C1_REC.Birth_Year has one value, like 1943, the procedure wooks. If C1_REC.Birth_Year has more than one value, like 1943, 1945 then the procedure does not work.
Data type of Birth_Year column is varchar2 (500 Char)
Data type of  NDVDL_BIRTH_YEAR column is Number (4).

Please advise.

Thanks a lot.

Excellent. This question can now be closed.

Slightwv and Walter Ritzel, thanks a lot.