2 Replies Latest reply: May 6, 2012 7:50 AM by ricardo.tomas RSS

    select with like for multiple results

    ricardo.tomas
      Hi,

      I need to find all the rows of a table that contain the values of another query in the table.
      My problem is that the table that I want to verify may contain a list of values and I’m not been able to think on an easy way to do this!

      Performance is also an issue because my table2 will have only a short list of values to look for but the table I will search will have a few million rows.

      The field that I need to search is a “VARCHAR2(4000 BYTE)” It is not indexed but probably I will need to create an index in it in case the best solution involves it!

      My example scenario:
      CREATE TABLE TEST_TABLE
      (id number,
      COL1 CHAR(25));
      
      CREATE TABLE TEST_TABLE2
      (COL1 CHAR(25));
      
      
      insert into TEST_TABLE (id,COL1) values (1,'1;2;3');
      insert into TEST_TABLE (id,COL1) values (2,'1');
      insert into TEST_TABLE2 (COL1)  values ('1');
      insert into TEST_TABLE2 (COL1) values ('2');
      
      
      select id from TEST_TABLE
      where (col1) in ( select col1 from TEST_TABLE2)
      My results with this are:
      Results:
      ID
      2
      I need the results to be ( because ID 1 also contains the values from table 2):
      Results:
      ID
      1
      2
      Thanks 
      Ricardo Tomás
        • 1. Re: select with like for multiple results
          Solomon Yakobson
          select  id
            from  test_table t1
            where exists (
                          select  1
                            from  test_table2 t2
                            where ';' || trim(t1.col1) || ';' like '%;' || trim(t2.col1) || ';%'
                         )
          /
          
                  ID
          ----------
                   1
                   2
          
          SQL> 
          Why column COL1 is defined as CHAR? Since CHAR is right blank padded you will have to trim. Use VARCHAR2:
          SQL> select  id
            2    from  test_table t1
            3    where exists (
            4                  select  1
            5                    from  test_table2 t2
            6                    where ';' || t1.col1 || ';' like '%;' || t2.col1 || ';%'
            7                 )
            8  /
          
                  ID
          ----------
                   1
                   2
          
          SQL> 
          SY.
          • 2. Re: select with like for multiple results
            ricardo.tomas
            Thanks :)

            I've created them as char by mistake! the idea was to be varchar2 to simulate the production tables!