DB2 Blog

A customer asked me to develop a function to check whether a character string was in fact numeric with the further stipulation that it be done in SQL. He reported that the documentation seemed to be lacking.

The DB2 LUW Information Center used to have a Reference section, where you would find the SQL statement reference along with everything else. This has been reorganized in DB2 9.5 to have a "Database Fundamentals" section. You will find the SQL reference information in there.

I figured the easiest thing to do would to use an exception handler. Unfortunately, the documentation provides nothing in the list of topics. The best example I could find was in the IF statement in the SQL section. (IF statement you say: "Of course!") There is almost nothing available for SQL/PL.

From snippets of code there, I developed this:

-- Invalid numeric character is SQLSTATE 22018
DECLARE not_num CONDITION FOR SQLSTATE '22018';
DECLARE EXIT HANDLER FOR not_num
SET smint = -1;
SELECT INTEGER(sTmp) INTO sTmpa FROM SYSIBM.SYSDUMMY1;

The next thing was I couldn't use this in a function. I'll write about functions and stored procedures next time.