Thread: What function can report whether the value of a string is a number?

1. Registered User
Join Date
Aug 2003
Posts
7

Unanswered: What function can report whether the value of a string is a number?

Hi.all

Who can tell me what function can report whether the value of a string is a number?

OS:Win2000
DB2:V7.2

Thanks!

2. Registered User
Join Date
Mar 2004
Location
Posts
513

Re: What function can report whether the value of a string is a number?

Originally posted by SYMBOL
Hi.all

Who can tell me what function can report whether the value of a string is a number?

OS:Win2000
DB2:V7.2

Thanks!
Could you just try to cast it and if it fails catch the SQL0420N?

ie. integer('aaa') fails
integer('3') succeeds

Not sure if this helps, I don't think there's a built-in that would give you a true/false type of answer.

Join Date
Jun 2002
Location
UK
Posts
525
You could write a function. Something like this...
Code:
CREATE FUNCTION IS_NUMERIC (ARG VARCHAR(2000))
RETURNS CHAR(5)
LANGUAGE SQL
DETERMINISTIC
------------------------------------------------------------------
-- SQL function to return determine if a a string is numeric
------------------------------------------------------------------
RETURN
WITH NUMERIC_ARRAY (NUM) AS
(
VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)
)
, FIND_NUMERICS (ISNUM, NUM_TEST, COUNTER) AS
(

SELECT 0
,      NUM
,      0
FROM   NUMERIC_ARRAY

UNION ALL

SELECT LOCATE (CAST(NUM_TEST AS CHAR(1)),(SUBSTR(ARG,COUNTER+1,1)))
,      NUM_TEST
,      COUNTER + 1
FROM   FIND_NUMERICS F
WHERE  COUNTER < LENGTH(ARG)

)
-- An 'ISNUM' value of 1 should be present for every char if all numerics
VALUES (
CASE WHEN LENGTH (ARG) > (SELECT COUNT(1) FROM FIND_NUMERICS WHERE ISNUM = 1)
THEN 'FALSE'
ELSE 'TRUE'
END
)
;

4. Registered User
Join Date
Aug 2003
Posts
7
Oh, I have know now!
Thanks !

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•