Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: is number

Re: is number

From: <eastking_at_my-deja.com>
Date: Fri, 15 Sep 2000 02:53:25 GMT
Message-ID: <8ps2ut$fmi$1@nnrp1.deja.com>

In article <OG4w5.6708$oc3.415894_at_news.flash.net>,   "Eric Givler" <egivler_at_flash.net> wrote:
> How about?
>
> create or replace function is_number( in_value in varchar2 ) return
 varchar2
> is
> retval varchar2(5);
> local_number number;
> begin
> begin
> --select to_number( in_value )
> -- into local_number
> -- from dual;
> local_number := to_number( in_value );
> retval := 'TRUE';
> exception
> when invalid_number then /* raised by ora-1722 if we use a sql
> statement */
> retval := 'FALSE';
> when value_Error then /* raised by pl/sql errors */
> retval := 'FALSE';
> end;
> return (Retval);
> end is_number;
> /
>
> rem qry_ex_table.sql
> column results format a10 trunc
> select id
> , value
> , is_number(id) results
> from ex_Table
> /
>
> select max(to_number(id)) + 1
> from ex_table
> where is_number( id ) = 'TRUE'
> /
>
> <eastking_at_my-deja.com> wrote in message news:8ppq47
 $nrm$1_at_nnrp1.deja.com...
> > hello , everyone here
> >
> > I have a table like following:
> >
> > Ex_table
> >
> > id value
> > 01 aaa
> > hh AAA
> > 02 bbb
> > ab ccc
> >
> >
> > I'd like to insert a row whose id is max(to_number(id))+1 into this
> > table . In this case , the id should be 3. But to_number('ab') will
> > cause a error.How can I achieve it by only a SQL command.
> >
> > Thanks in advance.
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Thanks . It's a good choice in this case. But the length of id column in my true table is 8 . Performance is a problem.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Sep 14 2000 - 21:53:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US