Select ... like (subselect) - Possible?
The poster wanted to match rows in tab1 whose column contains a substring stored in another table (tab2), i.e. a LIKE whose pattern comes from a sub-select, which Informix rejects. Suggestions included doing it in a stored procedure/4GL, a correlated EXISTS/sub-select trick with '%' || trim(col) || '%' (confirmed working on 7.3, but slow), and a REPLACE-based trick. The poster settled on the simple join form: select * from tab1, tab2 where tab1.col1 like "%" || trim(tab2.col2) || "%" — effectively a cartesian product, slow but it worked at the client site.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I've got a problem that needs to be solved urgently, because a colleague
of mine is waiting at a client's site for a solution...
This simple
select col1, col2 from tab1 where col1 like "%abc%"
is clear to me, but is it possible to combine the 'like' clause with a
sub-select?
E. g.:
select col1, col2 from tab1
where col1 like (select "%" || col3 || "%" from tab2)
(I already know that this select statement doesn't work, but I'm
interested in any other possible solutions.)
We want to retrieve all rows from tab1 where the content of col1
includes a specific criteria which is stored in another table. (tab1
holds addresses and tab2 holds special types of names and we want to
filter those addresses where some part of the name (not the name
itself!) equals a special name.)
Please reply as well directly to me: mailto:st@mr-informatik.de.
TIA for your fast help
and many regards,
Stephan.
--
Stephan Stresing
MR Informatik GmbH
mailto:st@mr-informatik.de
Sent via Deja.com http://www.deja.com/
Before you buy.
Hi!
You could build something like this with a stored procedure or 4gl, but
I don't think plain sql will do.
HTH
Michael
Stephan Stresing wrote:
> Hi,
> I've got a problem that needs to be solved urgently, because a colleague
> of mine is waiting at a client's site for a solution...
>
> This simple
>
> select col1, col2 from tab1 where col1 like "%abc%">
> is clear to me, but is it possible to combine the 'like' clause with a
> sub-select?
>
> E. g.:
>
> select col1, col2 from tab1
> where col1 like (select "%" || col3 || "%" from tab2)>
> (I already know that this select statement doesn't work, but I'm
> interested in any other possible solutions.)
>
> We want to retrieve all rows from tab1 where the content of col1
> includes a specific criteria which is stored in another table. (tab1
> holds addresses and tab2 holds special types of names and we want to
> filter those addresses where some part of the name (not the name
> itself!) equals a special name.)
>
> Please reply as well directly to me: mailto:st@mr-informatik.de.
>
> TIA for your fast help
> and many regards,
> Stephan.
>
> --
> Stephan Stresing
>
> MR Informatik GmbH
> mailto:st@mr-informatik.de
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
The following seems to work in 7.3 (to tell you the truth, I didn't think
that it would)
create table junk1 (
col1 smallint primary key,
col2 char(5),
col3 char(5)
);
create table junk2 (
col4 char(5)
);
insert into junk1 values (1, 'a','0');
insert into junk1 values (2, 'b','0');
insert into junk1 values (3, 'c','0');
insert into junk1 values (4, 'ca','0');
insert into junk1 values (5, 'cd','0');
insert into junk2 values ('0a0');
insert into junk2 values ('xca0');
select col2, col3 from junk1 a
where exists (
select 1 from junk2
where col4 like (select '%' || trim(col2) || '%' from junk1 b
where a.col1 = b.col1)); -- this join on primary key toreturn exactly 1 row
returns the following (appears to be what you need)
col2 col3
a 0
c 0
ca 0
Performance? Ha!
Rudy
Stephan Stresing wrote:
> Hi,
> I've got a problem that needs to be solved urgently, because a colleague
> of mine is waiting at a client's site for a solution...
>
> This simple
>
> select col1, col2 from tab1 where col1 like "%abc%">
> is clear to me, but is it possible to combine the 'like' clause with a
> sub-select?
>
> E. g.:
>
> select col1, col2 from tab1
> where col1 like (select "%" || col3 || "%" from tab2)>
> (I already know that this select statement doesn't work, but I'm
> interested in any other possible solutions.)
>
> We want to retrieve all rows from tab1 where the content of col1
> includes a specific criteria which is stored in another table. (tab1
> holds addresses and tab2 holds special types of names and we want to
> filter those addresses where some part of the name (not the name
> itself!) equals a special name.)
>
> Please reply as well directly to me: mailto:st@mr-informatik.de.
>
> TIA for your fast help
> and many regards,
> Stephan.
>
> --
> Stephan Stresing
>
> MR Informatik GmbH
> mailto:st@mr-informatik.de
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Stephan Stresing <stephan_stresing@my-deja.com> schrieb in im
Newsbeitrag:
> I've got a problem that needs to be solved urgently, because a
colleague
> of mine is waiting at a client's site for a solution...
Hope your colleague will not be waiting yet ;-)
> is clear to me, but is it possible to combine the 'like' clause
with a
> sub-select?
If I got your problem right the solution is quite simple:
select col1, col2 from tab1
where col1 in (select col3 from tab2
where col3 like "%abc%")
Reinhard
Hi Rudy,
your solution wasn't exactly the solution I was looking for. But I've
managed to adapt it that it matched my (and my colleague's) needs.
Thanks for your help!
Regards,
Stephan.
In article <38332062.C7C4899D@americasm01.nt.com>,
Rudy Fernandes <rferdy@americasm01.nt.com> wrote:
> The following seems to work in 7.3 (to tell you the truth, I didn't
think
> that it would)
>
> create table junk1 (
> col1 smallint primary key,
> col2 char(5),
> col3 char(5)
> );>
> create table junk2 (
> col4 char(5)
> );>
> insert into junk1 values (1, 'a','0');
> insert into junk1 values (2, 'b','0');
> insert into junk1 values (3, 'c','0');
> insert into junk1 values (4, 'ca','0');
> insert into junk1 values (5, 'cd','0');>
> insert into junk2 values ('0a0');
> insert into junk2 values ('xca0');>
> select col2, col3 from junk1 a
> where exists (
> select 1 from junk2
> where col4 like (select '%' || trim(col2) || '%' from junk1 b
> where a.col1 = b.col1)); -- this join on primarykey to
> return exactly 1 row
>
> returns the following (appears to be what you need)
>
> col2 col3
>
> a 0
> c 0
> ca 0
>
> Performance? Ha!
> Rudy
>
> Stephan Stresing wrote:
>
> > Hi,
> > I've got a problem that needs to be solved urgently, because a
colleague
> > of mine is waiting at a client's site for a solution...
> >
> > This simple
> >
> > select col1, col2 from tab1 where col1 like "%abc%"> >
> > is clear to me, but is it possible to combine the 'like' clause with
a
> > sub-select?
> >
> > E. g.:
> >
> > select col1, col2 from tab1
> > where col1 like (select "%" || col3 || "%" from tab2)> >
> > (I already know that this select statement doesn't work, but I'm
> > interested in any other possible solutions.)
> >
> > We want to retrieve all rows from tab1 where the content of col1
> > includes a specific criteria which is stored in another table. (tab1
> > holds addresses and tab2 holds special types of names and we want to
> > filter those addresses where some part of the name (not the name
> > itself!) equals a special name.)
> >
> > Please reply as well directly to me: mailto:st@mr-informatik.de.
> >
> > TIA for your fast help
> > and many regards,
> > Stephan.
> >
> > --
> > Stephan Stresing
> >
> > MR Informatik GmbH
> > mailto:st@mr-informatik.de
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>
--
Stephan Stresing
MR Informatik GmbH
mailto:st@mr-informatik.de
Sent via Deja.com http://www.deja.com/
Before you buy.
Hi Stephan,
I hope my solution is exactly that you want ;-)
And the select will be a little bit faster than previous one. ;-)
select unique tab1.col1,tab1.col2
from tab1,tab2
where replace(tab1.col1,trim(tab2.col3),"LongUnusualString") like"%LongUnusualString%"
Regards,
Eugene Nechayev
In article <810pqo$iog$1@nnrp1.deja.com>,
Stephan Stresing <stephan_stresing@my-deja.com> wrote:
> Hi Rudy,
> your solution wasn't exactly the solution I was looking for. But I've
> managed to adapt it that it matched my (and my colleague's) needs.
> Thanks for your help!
>
> Regards,
> Stephan.
>
> In article <38332062.C7C4899D@americasm01.nt.com>,
> Rudy Fernandes <rferdy@americasm01.nt.com> wrote:
> > The following seems to work in 7.3 (to tell you the truth, I didn't
> think
> > that it would)
> >
> > create table junk1 (
> > col1 smallint primary key,
> > col2 char(5),
> > col3 char(5)
> > );> >
> > create table junk2 (
> > col4 char(5)
> > );> >
> > insert into junk1 values (1, 'a','0');
> > insert into junk1 values (2, 'b','0');
> > insert into junk1 values (3, 'c','0');
> > insert into junk1 values (4, 'ca','0');
> > insert into junk1 values (5, 'cd','0');> >
> > insert into junk2 values ('0a0');
> > insert into junk2 values ('xca0');> >
> > select col2, col3 from junk1 a
> > where exists (
> > select 1 from junk2
> > where col4 like (select '%' || trim(col2) || '%' from junk1 b
> > where a.col1 = b.col1)); -- this join on primary> key to
> > return exactly 1 row
> >
> > returns the following (appears to be what you need)
> >
> > col2 col3
> >
> > a 0
> > c 0
> > ca 0
> >
> > Performance? Ha!
> > Rudy
> >
> > Stephan Stresing wrote:
> >
> > > Hi,
> > > I've got a problem that needs to be solved urgently, because a
> colleague
> > > of mine is waiting at a client's site for a solution...
> > >
> > > This simple
> > >
> > > select col1, col2 from tab1 where col1 like "%abc%"> > >
> > > is clear to me, but is it possible to combine the 'like' clause
with
> a
> > > sub-select?
> > >
> > > E. g.:
> > >
> > > select col1, col2 from tab1
> > > where col1 like (select "%" || col3 || "%" from tab2)> > >
> > > (I already know that this select statement doesn't work, but I'm
> > > interested in any other possible solutions.)
> > >
> > > We want to retrieve all rows from tab1 where the content of col1
> > > includes a specific criteria which is stored in another table.
(tab1
> > > holds addresses and tab2 holds special types of names and we want
to
> > > filter those addresses where some part of the name (not the name
> > > itself!) equals a special name.)
> > >
> > > Please reply as well directly to me: mailto:st@mr-informatik.de.
> > >
> > > TIA for your fast help
> > > and many regards,
> > > Stephan.
> > >
> > > --
> > > Stephan Stresing
> > >
> > > MR Informatik GmbH
> > > mailto:st@mr-informatik.de
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
> --
> Stephan Stresing
>
> MR Informatik GmbH
> mailto:st@mr-informatik.de
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Hi Eugene,
in the meantime I just came across a similar solution. It was much
easier than I thought... (I hope I remember the query correctly,
because I'm posting this from home...so I wasn't yet able to check
your solution, but it looks like you got it.) Here's mine:
select * from tab1, tab2
where tab1.col1 like "%" || trim(tab2.col2) || "%"
That retrieves all rows from tab1 where the pattern in tab2.col2 is
part of tab1.col1. That's it. It's not very fast (it's a carthesian
product with approx. 13 million rows), but effective. But who cares
if it's urgent? Not me! :-)))) It already worked at our client's site
and all are happy now...
Thanks to you
and all the others,
Stephan.
Eugene Nechayev wrote:
>
> Hi Stephan,
>
> I hope my solution is exactly that you want ;-)
> And the select will be a little bit faster than previous one. ;-)
>
> select unique tab1.col1,tab1.col2
> from tab1,tab2
> where replace(tab1.col1,trim(tab2.col3),"LongUnusualString") like> "%LongUnusualString%"
>
> Regards,
> Eugene Nechayev
>
> In article <810pqo$iog$1@nnrp1.deja.com>,
> Stephan Stresing <stephan_stresing@my-deja.com> wrote:
> > Hi Rudy,
> > your solution wasn't exactly the solution I was looking for. But I've
> > managed to adapt it that it matched my (and my colleague's) needs.
> > Thanks for your help!
> >
> > Regards,
> > Stephan.
> >
> > In article <38332062.C7C4899D@americasm01.nt.com>,
> > Rudy Fernandes <rferdy@americasm01.nt.com> wrote:
> > > The following seems to work in 7.3 (to tell you the truth, I didn't
> > think
> > > that it would)
> > >
> > > create table junk1 (
> > > col1 smallint primary key,
> > > col2 char(5),
> > > col3 char(5)
> > > );> > >
> > > create table junk2 (
> > > col4 char(5)
> > > );> > >
> > > insert into junk1 values (1, 'a','0');
> > > insert into junk1 values (2, 'b','0');
> > > insert into junk1 values (3, 'c','0');
> > > insert into junk1 values (4, 'ca','0');
> > > insert into junk1 values (5, 'cd','0');> > >
> > > insert into junk2 values ('0a0');
> > > insert into junk2 values ('xca0');> > >
> > > select col2, col3 from junk1 a
> > > where exists (
> > > select 1 from junk2
> > > where col4 like (select '%' || trim(col2) || '%' from junk1 b
> > > where a.col1 = b.col1)); -- this join on primary> > key to
> > > return exactly 1 row
> > >
> > > returns the following (appears to be what you need)
> > >
> > > col2 col3
> > >
> > > a 0
> > > c 0
> > > ca 0
> > >
> > > Performance? Ha!
> > > Rudy
> > >
> > > Stephan Stresing wrote:
> > >
> > > > Hi,
> > > > I've got a problem that needs to be solved urgently, because a
> > colleague
> > > > of mine is waiting at a client's site for a solution...
> > > >
> > > > This simple
> > > >
> > > > select col1, col2 from tab1 where col1 like "%abc%"> > > >
> > > > is clear to me, but is it possible to combine the 'like' clause
> with
> > a
> > > > sub-select?
> > > >
> > > > E. g.:
> > > >
> > > > select col1, col2 from tab1
> > > > where col1 like (select "%" || col3 || "%" from tab2)> > > >
> > > > (I already know that this select statement doesn't work, but I'm
> > > > interested in any other possible solutions.)
> > > >
> > > > We want to retrieve all rows from tab1 where the content of col1
> > > > includes a specific criteria which is stored in another table.
> (tab1
> > > > holds addresses and tab2 holds special types of names and we want
> to
> > > > filter those addresses where some part of the name (not the name
> > > > itself!) equals a special name.)
> > > >
> > > > Please reply as well directly to me: mailto:st@mr-informatik.de.
> > > >
> > > > TIA for your fast help
> > > > and many regards,
> > > > Stephan.
> > > >
> > > > --
> > > > Stephan Stresing
> > > >
> > > > MR Informatik GmbH
> > > > mailto:st@mr-informatik.de
> > > >
> > > > Sent via Deja.com http://www.deja.com/
> > > > Before you buy.
> > >
> > >
> >
> > --
> > Stephan Stresing
> >
> > MR Informatik GmbH
> > mailto:st@mr-informatik.de
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.