Re: SQL Query--cool multi-root wildcard search

Yeah, everyone pretty much nailed with the "like" and wildcard thing. If you've got several hundred search terms which may or may not change try this:

table 1 (contains the "root" you're looking for): named TMP (t_root varchar2(10))

table 2 (contains the data that you're searching on): named FOO (f_item_no number(10), f_data varchar2(100))

select	f_item_no,
	f_data
from	foo f,
	tmp t
where	f.f_data like t.t_root||'%';

FYI -Peter

On Wed, 15 May 1996 17:14:15 -0400, Shuzi Chen <chen_at_openmarket.com> wrote:

>How to write the SQL like the following?
>
> select * from test where name like 'hel[ply]'
> it will return the records which name is help, hell or hely.
>Sybase can do exactly like the above. I do not think the Oracle supports
>the [].
>
>Thanks in advance.
>--
>----------------------------------------------------------------------
>Shuzi Chen
>chen_at_openmarket.com
Received on Sun May 19 1996 - 00:00:00 CEST