| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Pattern maching question
Glenn,
You anyway have to use a function on contact_name, so the index will never
get used.
Thanks,
Amar
00-971-50-7883254
ts2017_at_emirates.com
amar_padhi_at_hotmail.com
amar_padhi_at_musclemail.com
-----Original Message-----
From: Glenn Travis [mailto:Glenn.Travis_at_wcom.com]
Sent: Tuesday, September 19, 2000 10:28 PM
To: ORACLE-L_at_fatcity.com; Oracledba_at_Lazydba. Com
Subject: RE: Pattern maching question
Thanks to all who replied. Most responses suggested using the substr function in one variation or another. I am trying to stay away from that syntax as the 'substr(contact_name,1,1)' function will not allow the select to use the index on contact_name.
Any other ideas? or should I stick with 'contact_name < 'A'' ?
> -----Original Message-----
> From: Ken Paulson [mailto:ken.paulson_at_wpg.faneuil.com]
> Sent: Tuesday, September 19, 2000 10:33 AM
> To: 'Glenn Travis'; ORACLE-L_at_fatcity.com; Oracledba_at_Lazydba. Com
> Subject: RE: Pattern maching question
>
>
>
> where substr(contact_name,1,1) BETWEEN '0' and '9'
>
> should work
>
> > -----Original Message-----
> > From: Josyula, Satyan [mailto:SJosyula_at_icfconsulting.com]
> > Sent: Monday, September 18, 2000 4:57 PM
> > To: 'Glenn Travis'; ORACLE-L_at_fatcity.com; Oracledba_at_Lazydba. Com
> > Subject: RE: Pattern maching question
> >
> >
> > You can use a query like this
> >
> > select * from contact where
> > translate(contact_name,'0123456789','0000000000') like '0%';
> >
> > HTH
> >
> > Satyam J
> >
> > > -----Original Message-----
> > > From: Glenn Travis [SMTP:Glenn.Travis_at_wcom.com]
> > > Sent: Monday, September 18, 2000 5:39 PM
> > > To: ORACLE-L_at_fatcity.com; Oracledba_at_Lazydba. Com
> > > Subject: Pattern maching question
> > >
> > > How can I get all the contacts which start with numbers?
> > > PLEASE NOTE: contact_name is a character field.
> > >
> > > select contact_name from contact
> > > where contact_name like '[0-9]%'
> > >
> > > does not work (like other dbms...)
> > >
> > > My workaround is;
> > >
> > > select contact_name from contact
> > > where contact_name < 'A'
> > >
> > > but I want to know the proper syntax for the first query.
> > >
> > > --------
> > > If you're bored, then visit the list's website: http://www.lazydba.com
> > > (updated daily)
> > > to unsubscribe, send a blank email to
> > oracledba-unsubscribe_at_quickdoc.co.uk
> > > to subscribe send a blank email to oracledba-subscribe_at_quickdoc.co.uk
> >
> > --------
> > If you're bored, then visit the list's website:
> http://www.lazydba.com (updated daily)
> to unsubscribe, send a blank email to oracledba-unsubscribe_at_quickdoc.co.uk
> to subscribe send a blank email to oracledba-subscribe_at_quickdoc.co.uk
>
>
 |
 |