Re: Range select
Date: 1997/10/22
Message-ID: <344E8980.7839497E_at_t-iii.com>#1/1
--------------553FE8676600EA0548BDA25B Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit
>SELECT ....... WHERE
> TRANSLATE(column, '12345', '99999') like '%9%'
If the given column already contains '9' then the results may not be as desired. Also, You are substituting '1' with '9' which means numbers beginning with '1' figure in results.
Try this.
SELECT ....... WHERE
Substr(column, 1, 1) Between 2 and 5;
Sunder Rajan.
AMARENDRA B NETTEM wrote:
> Pertti Kuusisto wrote:
> >
> > Dear colleauge,
> >
> > In some other db-systems it is posible to write a query like this:
> >
> > select .... where ... like '[2-5]%'
> >
> > that is to select a range with the 'like' keyword. Are there a way
of doing
> > that in oracle? (I know the 'between' keyword but that is not a
solution
> > because in my
> > problem the 'like' is hardcoded.)
> >
> > Sincerely,
> >
> > Pertti Kuusisto
>
> You can achive the above sql in Oracle using TRANSLATE function.
>
> SELECT ....... WHERE
> TRANSLATE(column, '12345', '99999') like '%9%'
>
> Hope this helps
>
> AMARENDRA
>
> --
>
> *****************************************************************************
>
> AMARENDRA B NETTEM 5039 N E River Road, Apt. 1A
> Certified Oracle DBA NORRIDGE, IL 60656
> Whittman-Hart Inc.,
> 311 South Wacker Drive, Suite 3500
> Chicago, IL 60606.
>
> Ph.No. (708) 583 9870 (H)
> (312) 913 6758 (W)
>
> E-mail:nettama_at_charlie.cns.iit.edu,
> anettem_at_whittman-hart.com
>
> Homepage: http://www.iit.edu/~nettama
>
>
> ***************************************************************************
>
> Opinions are mine and do not necessarily reflect those of
> Whittman-hart
> Inc.
--------------553FE8676600EA0548BDA25B Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit <HTML>
<I><TT>>SELECT ....... WHERE</TT></I>
<BR><I><TT>> TRANSLATE(column, '12345', '99999') like '%9%'</TT></I><I><TT></TT></I>
<P><TT>If the given column already contains '9' then the results may not be as desired. Also, You are substituting '1' with '9' which means numbers beginning with '1' figure in results.</TT><TT></TT>
<P><TT>Try this.</TT> <BR><TT>SELECT ....... WHERE</TT> <BR><TT>Substr(column, 1, 1) Between 2 and 5;</TT><I><TT></TT></I>
<P><TT>Sunder Rajan.</TT>
<P><I>AMARENDRA B NETTEM wrote:</I> [Quoted] <BLOCKQUOTE TYPE=CITE><I>Pertti Kuusisto wrote:</I> <BR><I>></I> <BR><I>> Dear colleauge,</I> <BR><I>></I> <BR><I>> In some other db-systems it is posible to write a query like this:</I> <BR><I>></I> <BR><I>> select .... where ... like '[2-5]%'</I> <BR><I>></I> <BR><I>> that is to select a range with the 'like' keyword. Are there away of doing</I>
<BR><I>> that in oracle? (I know the 'between' keyword but that is not a solution</I>
<BR><I>> because in my</I> <BR><I>> problem the 'like' is hardcoded.)</I> <BR><I>></I> <BR><I>> Sincerely,</I> <BR><I>></I> <BR><I>> Pertti Kuusisto</I><I></I>
<P><I>You can achive the above sql in Oracle using TRANSLATE function.</I><I></I>
<P><I>SELECT ....... WHERE</I>
<BR><I> TRANSLATE(column, '12345', '99999') like '%9%'</I><I></I>
<P><I>Hope this helps</I><I></I>
<P><I>AMARENDRA</I><I></I>
<P><I>--</I> <BR><I>******************************************************************************</I> <BR><I>AMARENDRA B NETTEM 5039 N E River Road, Apt. 1A</I>
<BR><I>Certified Oracle DBA NORRIDGE, IL 60656</I>
<BR><I>Whittman-Hart Inc.,</I> <BR><I>311 South Wacker Drive, Suite 3500</I> <BR><I>Chicago, IL 60606.</I><I></I>
<P><I>Ph.No. (708) 583 9870 (H)</I>
<BR><I> (312) 913 6758 (W)</I><I></I>
<P><I> E-mail:nettama_at_charlie.cns.iit.edu,</I> <BR><I> anettem_at_whittman-hart.com</I><I></I>
<P><I>Homepage: <A HREF="http://www.iit.edu/~nettama">http://www.iit.edu/~nettama</A></I><I></I>
<P><I>****************************************************************************</I> <BR><I>Opinions are mine and do not necessarily reflect those of Whittman-hart</I><BR><I>Inc.</I></BLOCKQUOTE>
<I> </I></HTML>
--------------553FE8676600EA0548BDA25B-- Received on Wed Oct 22 1997 - 00:00:00 CEST