Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: ISNUMERIC function

    Hi,

    Tried to create an ISNUMERIC functionality on db2.
    If the argument is a string, it has to display 0,for numeric it has to display 1.
    this is my code,
    Here for all values(both string & numeric) it is displaying 1 itself. can some one correct this.

    CREATE OR REPLACE FUNCTION ISNUMERIC (Source VARCHAR(40))
    RETURNS INTEGER
    LANGUAGE SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    RETURN
    CASE
    WHEN translate(source,'','0123456789.-+') <> ''
    THEN 1
    WHEN posstr(ltrim(source),'-') > 1
    OR posstr(ltrim(source),'+') > 1
    THEN 1
    WHEN length(rtrim(ltrim(translate(source,'','0123456789 .')))) > 1
    OR length(rtrim(ltrim(translate(source,'','0123456789 -+')))) > 1
    THEN 1
    WHEN posstr(ltrim(rtrim(translate(source,'','-+'))),' ') > 0
    THEN 1
    WHEN translate(source,'','.-+') = ''
    THEN 1
    ELSE 0
    END


    Thanks
    Last edited by HABBIE; 11-25-14 at 03:10.

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Code:
    create or replace function isnumeric (source varchar(40))
    returns int
    deterministic
    no external action
    contains sql
    begin
      declare l_dec decfloat;
      declare exit handler for sqlexception return 0;
      set l_dec=decfloat(source);
      return 1;
    end@
    Regards,
    Mark.

  3. #3
    Join Date
    Jul 2014
    Posts
    294
    thanks mark

Tags for this Thread

Posting Permissions

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