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.

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