We are facing an interesting problem. I want to write a casting function from char to int, that return the integer value. If the cast fails then the value 0 must be returned.

I did the following:
CREATE FUNCTION char2int(p_value varchar(100)) RETURNS INT
LANGUAGE SQL
BEGIN ATOMIC
DECLARE v_result INT;
SET RESULT = CAST(p_value AS INTEGER);
END

This function works, until a non number value is specified as input. We tried to catch this with declaring a condition fro sqlstate '22018' and define and UNDO handler.

This fails, when we create a procedure it (the error catching) works, but then I'm unable to return a result.

Is it impossible to catch errors in functions, or am I overlooking something.

Thanks,

RobP

You could use this UDF to verify if a char string is a valid representation of an integer.

I think you are going to have to write the UDf to check the parameter manually and then return accordingly. Something like this:

CREATE FUNCTION char2int(p_value varchar(100)) RETURNS INT
LANGUAGE SQL
BEGIN ATOMIC
DECLARE v_result INT;
DECLARE len int;
DECLARE cur_pos int;
DECLARE is_valid int;
DECLARE cur_char char(1);
DECLARE work_string varchar(100);

SET work_string = rtrim(ltrim(p_value));
set len = length(work_string);
set cur_pos = 1;
set is_valid = 1; -- assume it is valid
while (cur_pos <= len) do
set cur_char = substr(work_string,cur_pos,1);
IF (cur_char < '0' or cur_char > '9') then set is_valid = 0; end if;
set cur_pos = cur_pos + 1;
end while;

if (is_valid = 1)
THEN SET v_Result = integer(work_string);
else set v_Result = 0;
END IF;
return(v_Result);
END

Andy

Hi All,

Thanks for the reply's. This will solve the proble to convert to int. But I have to do more checkings. If possible I want to use DB2 logic as far as possible. Trapping error codes in procedures seems to do that job, but it is impossible to return a resul (I think). Is it really true that we cannot do error trapping in UDF's like in procedures. If this is so I think that IBM has missed this in their design. Why wouldn't you allow that???

Greetz,

RobP

No, you cannot do error handling in a UDF. I think the reason is because UDF are "in line" code (can be imbedded in other SQL), where Stored Procedures (SP) are not. You should be able to write an SP that has OUT parameters, then wrap it in a UDF to get what you want.

Andy

To validate the input string (p_value) you can do this:
1. Convert all invalid characters, including special characters (ABCD..Z@#$...) to null, using TRANSLATE and REPLACE
2. If previuos result is = to p_value, the string is valid. Otherwise, CASE result will be 0.
3. CAST will be applied to CASE result.

SELECT CAST ( CASE WHEN p_value =
REPLACE ( TRANSLATE ( UCASE ( p_value ),'','ABCDEF...' ),' ','' )
THEN p_value
ELSE '0' END AS INTEGER )
FROM SYSIBM.SYSDUMMY1

Note: 0 could be a valid result.
Saludos, ARLF.

Andy is right: put the conversion into a stored procedure and trap the error there. Then write a UDF that simply calls the procedure (copied Serge's version):