Home » SQL & PL/SQL » SQL & PL/SQL » creating multiple LIKE
creating multiple LIKE [message #271706] Tue, 02 October 2007 10:53 Go to next message
thapa
Messages: 15
Registered: September 2007
Junior Member
Hi all,
Can you please help me construct LIKE clause dynamically based on rows in some other table Soemthing like this:

select * from A a where a.description LIKE '%p%' OR LIKE '%q%'OR LIKE '%r%' [p,q,r are stored in Table B and can be obtained by doing 'Select desc from B'].

Just like select * from A a where a.description IN ( select b.desc from B b)for '=' search, is there any similar construct or syntax for LIKE search?

Thanks

Re: creating multiple LIKE [message #271708 is a reply to message #271706] Tue, 02 October 2007 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to dynamically build a string for your query and use dynamic SQL.

Regards
Michel
Re: creating multiple LIKE [message #271721 is a reply to message #271708] Tue, 02 October 2007 11:37 Go to previous messageGo to next message
thapa
Messages: 15
Registered: September 2007
Junior Member
Thanks Michel.
Pardon my ignorance. So If I create a pl sql function that returns String with LIKE parameters build together, is there a restriction in SQL syntax as in where I can place that function?

say my function is getLikeSyntax() that returns Like clause, can I construct something like this:
select * from A where getLikeSyntax(). I am very new to PLSql and whatever plsql functions I have used so far are all part of select invoked before FROM keyword in the query.

Thank you.
Re: creating multiple LIKE [message #271724 is a reply to message #271721] Tue, 02 October 2007 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do that.
You have to build a string with the WHOLE query and then use it using "execute immediate" statement or dbms_sql package.

Regards
Michel
Re: creating multiple LIKE [message #271790 is a reply to message #271706] Tue, 02 October 2007 23:38 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Like this?

create table a( description varchar2(10) );

create table b( descr varchar2(10) );

insert into a values ( 'aaa' );
insert into a values ( 'aba' );
insert into a values ( 'apa' );
insert into a values ( 'aqa' );
insert into a values ( 'ara' );
insert into a values ( 'pqr' );
insert into a values ( null  );

insert into b values ( 'p' );
insert into b values ( 'q' );
insert into b values ( 'r' );

commit;

select distinct a.*
from a, b
where  a.description like '%' || b.descr || '%'
;

DESCRIPTIO
----------
apa
pqr
ara
aqa


--
Joe Fuda
SQL Snippets

p.s. In the future please provide the CREATE TABLE commands, INSERT commands, and desired output for a simple test case with your question.

Re: creating multiple LIKE [message #272044 is a reply to message #271790] Wed, 03 October 2007 14:31 Go to previous messageGo to next message
thapa
Messages: 15
Registered: September 2007
Junior Member
Thats a pretty neat solution Joe. Essentially, is it doing?
select distinct a.*
from a, b
where a.description like '%'p'%' OR like '%'q'%' OR like '%'r'%'
;



Could someone please explain what does it mean to do
select a,b 
without actually joining them? I am interested in the performance issue because I will running the similar query in a table with about 20 millions records.

[Updated on: Wed, 03 October 2007 15:25]

Report message to a moderator

Re: creating multiple LIKE [message #272053 is a reply to message #272044] Wed, 03 October 2007 17:15 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member

Why not just try "select * from a,b" to see what it does?

select *
from   a, b
order by 1,2 ;

DESCRIPTIO DESCR
---------- ----------
aaa        p
aaa        q
aaa        r
aba        p
aba        q
aba        r
apa        p
apa        q
apa        r
aqa        p
aqa        q
aqa        r
ara        p
ara        q
ara        r
pqr        p
pqr        q
pqr        r
           p
           q
           r



Similarly, the following snippet illustrates how the "a.description like '%' || b.descr || '%'" clause works.

select
  a.description ,
  b.descr ,
  case
    when a.description like '%' || b.descr || '%' then 'Y'
    else 'N'
    end
    as a_like_b
from   a, b
order by 1, 2 ;

DESCRIPTIO DESCR      A
---------- ---------- -
aaa        p          N
aaa        q          N
aaa        r          N
aba        p          N
aba        q          N
aba        r          N
apa        p          Y
apa        q          N
apa        r          N
aqa        p          N
aqa        q          Y
aqa        r          N
ara        p          N
ara        q          N
ara        r          Y
pqr        p          Y
pqr        q          Y
pqr        r          Y
           p          N
           q          N
           r          N



--
Joe Fuda
SQL Snippets
Re: creating multiple LIKE [message #272611 is a reply to message #271706] Fri, 05 October 2007 13:16 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
how about trying INSTR. Something like:

select *
from a
where exists
(
select null
from b
where instr(a.desc,b.desc) > 0
)
/
Re: creating multiple LIKE [message #277810 is a reply to message #272611] Wed, 31 October 2007 14:28 Go to previous messageGo to next message
thapa
Messages: 15
Registered: September 2007
Junior Member
Thanks Kevin and SnippetyJoe for the solutions.
I am trying to fit the solution to my usecase where user enters the multiple values( by comma separating it) instead of values already being stored in a second table ( table b).
In order to do that, I first created a custom type
create or replace type myTableType as table of VARCHAR2(32767)


Next, I created a pl Sql function to convert comma separated value to a table
create or replace function str2tbl( p_str in varchar2 ) return 
  myTableType 
   as
        l_str   varchar2(32767) default p_str || ',';
        l_n        number;
        l_data    myTableType := myTabletype();
    begin
        loop
            l_n := instr( l_str, ',' );
            exit when (nvl(l_n,0) = 0);
            l_data.extend;
            l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
            l_str := substr( l_str, l_n+1 );
        end loop;
        return l_data;
   end;



With IN search I could easily do
IN (             
                                select *
                                from  THE
                                (
                                   select cast( str2tbl(:var_holding_user_entered_csv_string ) as mytableType ) 
                                     from dual 
                                ) 


How do i use it with instr() function for LIKE search?

Thanks


Re: creating multiple LIKE [message #277844 is a reply to message #271706] Wed, 31 October 2007 19:26 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
well, if your goal is in part to parse a delimited string into its components, and in a form that looks like a table, then you can always use the following trick:

lets say your string is 'a,b,c'
comma is the delimiter of course

try executing this:

select a.cnt string_pos
      ,substr(','||'a,b,c'||','
             ,instr(','||'a,b,c'||',',',',1,a.cnt)+1
             ,instr(','||'a,b,c'||',',',',1,a.cnt+1)-instr(','||'a,b,c'||',',',',1,a.cnt)-1
             ) astring
from (
       select rownum cnt
       from dual
       connect by level <= nvl(length('a,b,c')-length(replace('a,b,c',','))+1,0)
     ) a
/


now that you have the individual strings, you can always do a like search with each. You might then do something like this:

with
   search_list as (
                     select a.cnt string_pos
                           ,substr(','||'a,b,c'||','
                                  ,instr(','||'a,b,c'||',',',',1,a.cnt)+1
                                  ,instr(','||'a,b,c'||',',',',1,a.cnt+1)-instr(','||'a,b,c'||',',',',1,a.cnt)-1
                                  ) search_term
                     from (
                            select rownum cnt
                            from dual
                            connect by level <= nvl(length('a,b,c')-length(replace('a,b,c',','))+1,0)
                          ) a
                  )
  ,string_list as (
                    select 'a,e,f,c,g' string_to_search
                    from dual
                  )
select string_list.string_to_search
      ,search_list.search_term
      ,instr(string_list.string_to_search,search_list.search_term) found_pos
from search_list
    ,string_list
/

Is this helping at all, Kevin
Re: creating multiple LIKE [message #278097 is a reply to message #277844] Thu, 01 November 2007 15:48 Go to previous messageGo to next message
thapa
Messages: 15
Registered: September 2007
Junior Member
Thanks Kevin. Works perfect!
Tried with blank and works fine too. What happens if the value passed is null?
Re: creating multiple LIKE [message #278100 is a reply to message #271706] Thu, 01 November 2007 16:14 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
you tell us! Cool
Re: creating multiple LIKE [message #278103 is a reply to message #278100] Thu, 01 November 2007 16:29 Go to previous messageGo to next message
thapa
Messages: 15
Registered: September 2007
Junior Member
works Cool
select a.cnt string_pos
      ,substr(','||null||','
             ,instr(','||null||',',',',1,a.cnt)+1
             ,instr(','||null||',',',',1,a.cnt+1)-instr(','||null||',',',',1,a.cnt)-1
             ) astring
from (
       select rownum cnt
       from dual
       connect by level <= nvl(length(null)-length(replace(null,','))+1,0)
     ) a

Kevin, I cannot decipher your logic though. What exactly is happening here Confused

Re: creating multiple LIKE [message #278106 is a reply to message #271706] Thu, 01 November 2007 17:20 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Ok

this line counts the number of elements in your delimited string.

       length('a,b,c')-length(replace('a,b,c',','))+1

  length('a,b,c') = 5 (doh!)
  length(replace('a,b,c'),',') = length('abc') = 3


so

length('a,b,c')-length(replace('a,b,c',','))+1
              5-length(        'abc'       )+1
              5-3                           +1
= 3

which is the number of elements in 'a,b,c'

This next line is a neat row generator trick I picked up from somewhere on the internet several years back.

       select rownum cnt
       from dual
       connect by level <= nvl(length('a,b,c')-length(replace('a,b,c',','))+1,0)


It abuses the connect by construct to deliver some number of rows. I don't really grasp why it works (I am not that smart), but I don't have to know how a hammer is made in order to pound nails.

Consider this:

SQL> l
  1  select rownum cnt
  2  from dual
  3* connect by level <= 3
SQL> /

       CNT
----------
         1
         2
         3

3 rows selected.

SQL


So this is just a row generator that generates however many silly rows you ask for.

The substr expression is just an ordinary way to pluck elements out of a delimited string using only sql. One trick I use with this is to add the delimliter character to the front and back of the string we are plucking elements out of so that the logic is simplified. No special logic needed for the first and last elements of the string because we have a FAT or WIDE list rather than a normalized list. Every element in the list is preceeded by a delimeter and also followed by a delimiter. So...


'a,b,c'  becomes  ','||'a,b,c'||','  =  ',a,b,c,'


now it is possible to use a simple substr expression (it just looks complicated) to get elements out of the string. If we pretend the dollarsign below is some variable that contains a delimlited string then the following expressions will extract element1 element2 and element3 respectively:

substr($,instr($,',',1,1)+1,instr($,',',1,2)-instr($,',',1,1)-1) element1
substr($,instr($,',',1,2)+1,instr($,',',1,3)-instr($,',',1,2)-1) element2
substr($,instr($,',',1,3)+1,instr($,',',1,4)-instr($,',',1,3)-1) element3


I will let you plug in a string for the dollarsign and do a desk check of the logic. Just keep reducing expressions till you get an answer. that way you can see how the string math works.

putting it all together we have this:

select a.cnt string_pos
      ,substr(','||null||','
             ,instr(','||null||',',',',1,a.cnt)+1
             ,instr(','||null||',',',',1,a.cnt+1)-instr(','||null||',',',',1,a.cnt)-1
             ) astring
from (
       select rownum cnt
       from dual
       connect by level <= nvl(length(null)-length(replace(null,','))+1,0)
     ) a


We count the number of elements in the string, then we use the connect by trick to generate a number of silly looking rows equal to the number of elements we just counted. We then pass over these silly looking rows and use the numeric ID of each row to pluck an element out of our delimilted string.

When we pass over silly row number 1, we execute the substring expression and get the first element from the string. When we pass over silly row number 2 we execute the substring expression and pluck out element number 2, silly row three provides element number 3.

The result is a set of rows each of which has one of the elements in your string.

Do you understand? Kevin
Re: creating multiple LIKE [message #278249 is a reply to message #278106] Fri, 02 November 2007 10:06 Go to previous messageGo to next message
thapa
Messages: 15
Registered: September 2007
Junior Member
Thanks a lot Kevin. Never thought of using the rowcount info to avoid any loop Smile
Re: creating multiple LIKE [message #278256 is a reply to message #278249] Fri, 02 November 2007 10:22 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
thanks,

yes, I find this a handy way to convert a delimited string into a table without resorting to table functions.

Kevin
Re: creating multiple LIKE [message #278440 is a reply to message #277810] Sun, 04 November 2007 03:55 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Wow, always good to see 8.0 syntax Wink

IN (             
     select *
     from  THE
           (
             select cast( str2tbl(:var_holding_user_entered_csv_string ) as mytableType ) 
             from dual 
           )

These days you might prefer:

IN (
     SELECT column_value
     FROM   TABLE(str2tbl(:var_holding_user_entered_csv_string))
   )

More fun with string splitters here.
Previous Topic: Problem with LIKE
Next Topic: Constraints over multiple rows?
Goto Forum:
  


Current Time: Fri Dec 02 12:20:39 CST 2016

Total time taken to generate the page: 0.10919 seconds