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

"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

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