I would content that you are both correct... For
pl/sql, then not performing a select-from-dual ie
using a 'is_number' routine will be quicker...But you
if you want to get all the rows from a table which are
numeric ie
select *
from my_big_table
where "col is numeric"
then using the sql approach will out perform the plsql
one. I've seen claims that in 9i all of this context
switching stuff will disappear - but I'll believe that
when I see it :-)
hth
connor
- Diana_Duncan_at_ttpartners.com wrote: >
> Jared,
>
> That's absolutely true, now. In the past (don't ask
> me when, I think it
> was version 7 something), I found that exceptions
> for some reason took a
> long time to process. I haven't cured myself of the
> habit of avoiding
> them, yet. :)
>
> Thanks for the reminder!
>
> Diana Duncan
> TITAN Technology Partners
> One Copley Parkway, Ste 540
> Morrisville, NC 27560
> VM: 919.466.7337 x 316
> F: 919.466.7427
> E: Diana_Duncan_at_ttpartners.com
>
>
>
>
>
> Jared Still
>
>
> <jkstill_at_cybc To:
> ORACLE-L_at_fatcity.com, Diana_Duncan_at_ttpartners.com
>
> on.com> cc:
>
>
> Fax to:
>
>
> 05/21/2001 Subject:
> Re: Check for number only
>
> 05:24 PM
>
>
>
>
>
>
>
>
>
>
>
>
>
> Diane,
>
> If you rely on Oracle to do the checking, it will
> be much faster.
>
> Jared
>
>
> create or replace function is_number( chk_data_in
> varchar2 )
> return boolean
> is
> dummy number(38,4);
> begin
> dummy := to_number(chk_data_in);
> return true;
> exception
> when value_error then
> return false;
> when others then
> raise;
> end;
> /
>
> show errors function is_number
>
>
> declare
> v_test varchar2(10) := '1E';
> begin
> if is_number(v_test) then
> dbms_output.put_line(v_test ||
> ' is a number');
> else
> dbms_output.put_line(v_test ||
> ' is NOT a number');
> end if;
> end;
> /
>
>
> On Friday 18 May 2001 11:27,
> Diana_Duncan_at_ttpartners.com wrote:
> > You could always write your own function. Or you
> could do something
> using
> > the replace() function, maybe like this (again,
> untested):
> >
> > select circ_id, circ_num from circuit
> > where replace(circ_num,
> >
>
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890',
> >
>
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')
> is not null;
> >
> > Depending on your data, you may need to check for
> punctuation and special
> > characters as well.
> >
> > HTH,
> >
> > Diana Duncan
> > TITAN Technology Partners
> > One Copley Parkway, Ste 540
> > Morrisville, NC 27560
> > VM: 919.466.7337 x 316
> > F: 919.466.7427
> > E: Diana_Duncan_at_ttpartners.com
> >
> >
> >
> > Glenn Travis
> > <c-glenn.travis To:
> Multiple recipients of
> > list ORACLE-L <ORACLE-L_at_fatcity.com> @wcom.com>
> cc:
> > Sent by: Fax to:
> > root_at_fatcity.co
> Subject: Check for number
> > only m
> >
> >
> > 05/18/2001
> > 12:17 PM
> > Please respond
> > to ORACLE-L
> >
> >
> >
> >
> >
> >
> > I have a table with a column of varchar2(50)
> datatype. We are building a
> > new table and the values in this column should all
> be numbers. I need to
> > query the table to find all rows which have a
> value in this column which
> is
> > not a valid number.
> >
> > Table:
> > circuit (circ_id (primary_key) number, circ_num
> varchar2(50))
> >
> > Example: find all values in circ_num which are
> not numbers.
> > how do I do something like 'select circ_id,
> circ_num from circuit where
> > circ_num not valid to_number(circ_num)'
> >
> > Is there a function to check this?
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author: Glenn Travis
> > INET: c-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
> > also send the HELP command for other information
> (like subscribing).
>
>
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
>
=== message truncated ===
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Get your free @yahoo.co.uk address at
http://mail.yahoo.co.uk
or your free @yahoo.ie address at
http://mail.yahoo.ie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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
also send the HELP command for other information (like subscribing).
Received on Tue May 22 2001 - 03:08:34 CDT
