Re: Check for number only

Diane,

Musta been a bug, as this was originally done on 7.3.something.

Jared

On Monday 21 May 2001 15:22, 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
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.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).