Test if numeric in DB400

Easy way to tell if a field has numeric data with DB400 / SQL400 / AS400 / iSeries. Compare to IsNumeric.

SELECT MYSCHEMA.ISNUMERIC(‘aa’) FROM SYSIBM.SYSDUMMY1;
SELECT MYSCHEMA.ISNUMERIC(‘6’) FROM SYSIBM.SYSDUMMY1;

DROP FUNCTION MYSCHEMA.ISNUMERIC;

CREATE FUNCTION MYSCHEMA.ISNUMERIC (PARALPHA VARCHAR(32)) RETURNS INTEGER
LANGUAGE SQL
RETURN
CASE
WHEN LENGTH(TRIM(TRANSLATE(PARALPHA, ‘ ‘, ‘1234567890’))) = 0 THEN 1
ELSE 0
END;

Test if numeric in DB400

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s