Re: regular expressions in query under oracle?

In article <7dceua$i1c$1_at_nnrp1.dejanews.com>,   JD Austin <jd_at_austin.centergate.com> wrote:
> I have a database field that is a list of integers.
> groupRead="05,27,10,810,45,15";
>
> The table associated to the groupRead field has a primary key of
> idnumber which is an integer.
>
> Im trying to return the idnumber's of any records that contain a known
> idnumber.
>
> The queries I have attempted are:
>
> select idnumber from lists where '10' in groupRead;
> This almost works, but only returns records where there is ONLY 10 listed.
>
> select idnumber from lists where instr(groupRead,'10')<>0;
>
> This query returns too much.. giving me records that contain the number 10,
> but the ones that contain 810 (and not 10) too.
>
> I assume I can probably use a regular expression in the where portion of the
> clause... Anyone know how this is done in Oracle8?
>
> JD
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

While I couldnt get LIKE and regular expressions to do what I wanted, I did find a solution...

by adding a leading and trailing ',' to the list I can search for a unique string: ',48,13,23,44,10,18,77,'

select idnumber from lists where instr(groupRead,',10,')>0;

Though I have a feeling that this WONT use an index.. is there a faster way to do it?

JD

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Mar 25 1999 - 01:39:17 CST