Postgres Pro
Downloads
Home   >   mailing lists

Thread: selecting recs based on a tmp tbl vals that are wildcarded ?

selecting recs based on a tmp tbl vals that are wildcarded ?

From
"Gauthier, Dave"
Date:

I have a temp table containg wildcarded strings and I want to select values froma different table using “like” against all those wildcarded values.  Here’s the example...

 

 

create temporary table match_these (val varchar(32));

insert into match_these (val) values (‘jo%’);

insert into match_these (val) values (‘%denn_’);

insert into match_these (val) values (‘alt%’);

 

create table footable (name varchar(32));

(insert a bunch of records)

 

Now...

 

select * from footable where name in (select val from match_these)

 

... won’t work because “in” implies equality.  I want something like...

 

select * from footable where name like (select val from match_these)

 

... but that doesn’t work because the subquery returns more than one row :-(

 

Any elegant sql solutions out there before I brute-force a solution ?

 

Thanks in Advance !

Re: selecting recs based on a tmp tbl vals that are wildcarded ?

From
Tom Lane
Date:
"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> select * from footable where name in (select val from match_these)

> ... won't work because "in" implies equality.  I want something like...

> select * from footable where name like (select val from match_these)

What you need is

 select * from footable where name ~~ any (select val from match_these)

It would probably be clearer to write

 select * from footable where name like any (select val from match_these)

but the ANY syntax requires an operator name, so you have to write the
operator equivalent for LIKE.

            regards, tom lane

Re: selecting recs based on a tmp tbl vals that are wildcarded ?

From
Harald Fuchs
Date:
In article <482E80323A35A54498B8B70FF2B8798003E5AC7099@azsmsx504.amr.corp.intel.com>,
"Gauthier, Dave" <dave.gauthier@intel.com> writes:

> I have a temp table containg wildcarded strings and I want to select values
> froma different table using ?like? against all those wildcarded values.  Here?s
> the example...

> create temporary table match_these (val varchar(32));

> insert into match_these (val) values (?jo%?);

> insert into match_these (val) values (?%denn_?);

> insert into match_these (val) values (?alt%?);

> create table footable (name varchar(32));

> (insert a bunch of records)

> Now...

> select * from footable where name in (select val from match_these)

> ... won?t work because ?in? implies equality.  I want something like...

> select * from footable where name like (select val from match_these)

Why don't you use a simple join?  Something like

  SELECT f.name
  FROM footable f
  JOIN match_these m ON f.name ~~ m.val