Results 1 to 4 of 4
  1. #1
    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. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    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. #4
    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
  •