Re: Alternative to the LIKE command

From: Al Reid <areidjr_at_reidHyphenhome.com>
Date: Thu, 12 Feb 2004 02:02:45 GMT
Message-ID: <99BWb.13832$M8.1174@nwrdny02.gnilink.net>

<OakRogbak_erPine_at_yahoo.com> wrote in message news:13fdc9b4.0402111412.431115cd_at_posting.google.com...
> One of our developers is trying to figure out how to search a string
> for a partial match. In other words, if you had a table such as
>
> NAME (Varchar2) TIDS (varchar2)
> --------------- --------------
> Joe Black
> 123123,432432,324234,764565,4334534,344353,45715435,35338
> Ben Hur
> 123756,324342,8237482,975924,145238,2934283,2348248,23423
> Tom Thumb
> 872834,93124,873247,092243,23432,823472,29349,7502849
>
>

Does the data in TIDS represent 8 distinct numbers separated by commas? If so, perhaps the data base needs some normalization before worrying about query performance. Otherwise, LIKE seems to be a reasonable approach.

> Is there another way to rewrite
>
> select NAME from MYTABLE where TIDS like '%324234%';
>
> so that you get better performance, assuming a great number of rows?
> Can the owa_pattern.match be used here?
>
> Thanks.
Received on Wed Feb 11 2004 - 20:02:45 CST