RE: [sqr-users] Checking for numeric datatype

["George Jansen" <GJANSEN@aflcio.org>]

Yep.

You have essentially two choices:

1. Craft a regular expression that will cover all possible number
formats. Oracle 10g supports REs in SQL now. This can get ugly if you
aren't limiting yourself to integer and decimal.

2. Throw it up against the wall and see what sticks, i.e. put together
a PL/SQL procedure that will use an EXCEPTION WHEN block to handle it. I
suspect that in the long run this would hurt performance. But what the
heck:


SQL> get /tmp/its_a_number
  1  CREATE OR REPLACE FUNCTION its_a_number
  2     (p_something IN VARCHAR2)
  3  RETURN VARCHAR2
  4  AS
  5    v_dummy NUMBER;
  6  BEGIN
  7    v_dummy := p_something;
  8    RETURN 'Yes';
  9  EXCEPTION WHEN OTHERS THEN
 10    RETURN 'No';
 11* END its_a_number;
SQL> /

Function created.

SQL> select its_a_number('1e25') from dual;

ITS_A_NUMBER('1E25')
--------------------------------------------------------------------------------
Yes

SQL> select its_a_number('notsofast') from dual;

ITS_A_NUMBER('NOTSOFAST')
--------------------------------------------------------------------------------
No

SQL> select its_a_number(Sysdate) from dual;

ITS_A_NUMBER(SYSDATE)
--------------------------------------------------------------------------------
No


>>> "Elvis Pressly" <elvisdman@hotmail.com> 8/30/2006 3:58 PM >>>

You cannot use this function. What if the value is negative? I believe
the 
question should
also include how to test for all numeric values.

>From: "Kaithi, Murali" <Murali.Kaithi@spencergifts.com>
>Reply-To: "This list is for discussion about the SQR database 
>reportinglanguage from Hyperion Solutions." <sqr-users@sqrug.org>
>To: "This list is for discussion about the SQR database
reportinglanguage 
>fromHyperion Solutions." <sqr-users@sqrug.org>
>Subject: RE: [sqr-users] Checking for numeric datatype
>Date: Wed, 30 Aug 2006 14:03:00 -0400
>
>Move $var to #var
>If #var >= 0
>  let $is_numeric = 'T'
>Else
>  let $is_numeric = 'F'
>End-if
>
>Murali..
>
>-----Original Message-----
>From: sqr-users-bounces+murali.kaithi=spencergifts.com@sqrug.org 
>[mailto:sqr-users-bounces+murali.kaithi=spencergifts.com@sqrug.org]
On
>Behalf Of Vaidyanathan, Ramakrishnan (GE, Corporate,consultant)
>Sent: Wednesday, August 30, 2006 1:57 PM
>To: sqr-users@sqrug.org 
>Subject: RE: [sqr-users] Checking for numeric datatype
>
>the table is a custom table thats built for a specific purpose to
hold
>certain transactions. its not a oracle provided table.
>
>-----Original Message-----
>From: sqr-users-bounces+ramakrishnan.vaidyanathan=ge.com@sqrug.org 
>[mailto:sqr-users-bounces+ramakrishnan.vaidyanathan=ge.com@sqrug.org]On

>Behalf Of Knapp, Richard
>Sent: Wednesday, August 30, 2006 1:53 PM
>To: This list is for discussion about the SQR database
reportinglanguage
>fromHyperion Solutions.
>Subject: RE: [sqr-users] Checking for numeric datatype
>
>
>
>Describe the source table.
>
>Richard Knapp
>AITS - Reporting
>University of Missouri
>Locust Street Building
>Columbia, MO 65201
>573-882-8856
>knappr@umsystem.edu 
>
>
>-----Original Message-----
>From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org 
>[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
>Vaidyanathan, Ramakrishnan (GE, Corporate,consultant)
>Sent: Wednesday, August 30, 2006 12:45 PM
>To: sqr-users@sqrug.org 
>Subject: [sqr-users] Checking for numeric datatype
>
>Hi,
>    Is there any function in sqr to check whether the value retireved
>from the database field is a numeric or not..some thing like is
>numeric..I am retrieving the database field to a variable and I need
to
>check whether that field is a numeric or not. Pls help Thanks Ram
>
>_______________________________________________
>sqr-users mailing list
>sqr-users@sqrug.org 
>http://www.sqrug.org/mailman/listinfo/sqr-users 
>
>_______________________________________________
>sqr-users mailing list
>sqr-users@sqrug.org 
>http://www.sqrug.org/mailman/listinfo/sqr-users 
>
>_______________________________________________
>sqr-users mailing list
>sqr-users@sqrug.org 
>http://www.sqrug.org/mailman/listinfo/sqr-users 
>
>________________________________________________________________________
>This Email has been scanned for all viruses by PAETEC Email Scanning 
>Services, utilizing MessageLabs proprietary SkyScan infrastructure.
For 
>more information on a proactive anti-virus service working around the

>clock, around the globe, visit http://www.paetec.com.
>________________________________________________________________________
>
>_______________________________________________
>sqr-users mailing list
>sqr-users@sqrug.org 
>http://www.sqrug.org/mailman/listinfo/sqr-users 



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users