RE: Pattern maching question

You could try something like:

  contact_name between '10000000' and '9ZZZZZZZ'

At 11:35 AM 9/19/00 -0800, you wrote:
>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
> >
> >
>
>--
>Author: Glenn Travis
> INET: Glenn.Travis_at_wcom.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
Received on Tue Sep 19 2000 - 16:04:30 CDT