Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    14

    Unanswered: help in SQL function

    Can anyone help in DB2 SQL?

    DEPT_ID(CHAR data type)
    2000
    2100
    2200_ABC
    2300_CDE
    2400_FGH

    I need only numerics to be displayed(all characters aftre underscore should be truncated)?TRUNCATE does'nt work.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can try something like:

    CREATE FUNCTION get_digits(INTEXT varchar(20))
    RETURNS varchar(20) language SQL deterministic NO EXTERNAL ACTION CALLED ON NULL INPUT
    BEGIN ATOMIC
    DECLARE outText VARCHAR(100);
    DECLARE strLength, counter int;
    DECLARE First_Char_is_M, Char_Count int;
    DECLARE currentChar CHAR;

    set intext = rtrim(ltrim(intext));

    SET outText = '';
    SET strLength = LENGTH(inText);
    SET counter = 1;

    IF (inText is NULL)
    THEN RETURN NULL;
    END IF;

    WHILE counter <= strLength DO
    SET currentChar = CHAR(LCASE(SUBSTR(inText,counter,1)));
    IF ((currentChar >= '0') AND (currentChar <= '9'))
    THEN set outText = outText || VARCHAR(currentChar);
    ELSE SET counter = strLength;
    END IF;

    SET counter = counter + 1;
    END WHILE;
    RETURN outText;
    END


    HTH

    Andy

Posting Permissions

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