This discussion is archived
2 Replies Latest reply: May 6, 2012 5:50 AM by 654930 RSS

select with like for multiple results

654930 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
    654930 Newbie
    Currently Being Moderated
    Thanks :)

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points