Home » SQL & PL/SQL » SQL & PL/SQL » creating multiple LIKE
| creating multiple LIKE [message #271706] |
Tue, 02 October 2007 10:53  |
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 #271721 is a reply to message #271708] |
Tue, 02 October 2007 11:37  |
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 #271790 is a reply to message #271706] |
Tue, 02 October 2007 23:38 |
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 |
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 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 |
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 #277810 is a reply to message #272611] |
Wed, 31 October 2007 14:28 |
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 |
 |
Kevin Meade
Messages: 2103
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 #278103 is a reply to message #278100] |
Thu, 01 November 2007 16:29 |
thapa
Messages: 15
Registered: September 2007
|
Junior Member |
|
|
works 
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 
|
|
|
|
| Re: creating multiple LIKE [message #278106 is a reply to message #271706] |
Thu, 01 November 2007 17:20 |
|
Kevin Meade
Messages: 2103
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 #278440 is a reply to message #277810] |
Sun, 04 November 2007 03:55 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
Wow, always good to see 8.0 syntax 
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.
|
|
|
|
Goto Forum:
Current Time: Tue Feb 18 10:49:22 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
