RE: [sqr-users] How to check 'CHAR' field to find out if it contains all Numeric?

[James Womeldorf <jwomeldo@fastenal.com>]

If you are trying to determine if the string contains a legal number
(positive or negative, with or without a decimal point) this is a bit more
complicated than just checking the digits.  I would let the database make
the determination.

The following code is specific to Oracle but I expect 'decode' and
'to_number' and the table 'dual' have equivalents in DB.  It determines
whether the string sent in actually represents a legitimate number.
The results of the SHOW statements are shown as comments following the SHOW
lines.

BEGIN-PROGRAM

let $X = ''
do IsNumeric($X, $Result)
SHOW $X ' ' $Result
! N

let $X = ' 2 '
do IsNumeric($X, $Result)
SHOW $X ' ' $Result
! 2  Y

let $X = '1h3214.32'
do IsNumeric($X, $Result)
SHOW $X ' ' $Result
!1h3214.32 N

let $X = '-132.14.32'
do IsNumeric($X, $Result)
SHOW $X ' ' $Result
!-132.14.32 N

let $X = '-132.1432f'
do IsNumeric($X, $Result)
SHOW $X ' ' $Result
!-132.1432f N

let $X = '-132.14'
do IsNumeric($X, $Result)
SHOW $X ' ' $Result
!-132.14 Y

let $X = '1.3214.32'
do IsNumeric($X, $Result)
SHOW $X ' ' $Result
!1.3214.32 N

END-PROGRAM

begin-procedure IsNumeric($X, :$Result) 
begin-select on-error=WOOPS($Result)
DECODE(to_number($X),NULL,'N','Y') &YN
    let $Result = &YN
from dual
end-select            
end-procedure

begin-procedure WOOPS(:$Result)          
let $Result = 'N'
end-procedure               

Jim

-----Original Message-----
From: sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org]On Behalf Of
Tajinder Chahal
Sent: Thursday, October 14, 2004 3:17 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] How to check 'CHAR' field to find out if it
contains all Numeric?


Hello Everyone,

We are on DB2 database.
I need to find out if a 'CHAR' field contains all 'Numerics' or not?


_______________________________________________
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