See prior e-mail for context of this question...


Here is the text of the function:

CREATE FUNCTION NumericTextDiff (v_pValue1 VARCHAR(255),
v_pValue2 VARCHAR(255))
RETURNS DECIMAL(25,10)
LANGUAGE SQL
BEGIN ATOMIC
DECLARE v_vNumber1 decimal(25,10);
DECLARE v_vNumber2 decimal(25,10);
DECLARE v_NumFlag1 INTEGER;
DECLARE v_NumFlag2 INTEGER;
DECLARE v_retval decimal(25,10);
DECLARE v_value VARCHAR(255);
--

SET v_value = DigitsOnly(v_pValue1);
IF v_value is null OR v_value = 'NOT NUMERIC' THEN
RETURN 0.0;
END IF;

SET v_vNumber1 = COALESCE(DECIMAL(v_value, 25, 10), 0);

SET v_value = DigitsOnly(v_pValue2);
IF v_value is null or v_value = 'NOT NUMERIC' THEN
RETURN 0.0;
END IF;

SET v_vNumber2 = COALESCE(DECIMAL(v_value, 25, 10), 0);

RETURN (v_vNumber2-v_vNumber1);
--
END @

The invoked function DigitsOnly is defined as:

CREATE FUNCTION DigitsOnly( v_pDirtyNumeric VARCHAR(4000) )
RETURNS VARCHAR(4000)
LANGUAGE SQL
BEGIN ATOMIC
--
DECLARE v_vDirtyPos INTEGER;
DECLARE v_vDirtyLen INTEGER;
DECLARE v_vDirtyChar VARCHAR(1);
DECLARE v_vCleanNumeric VARCHAR(4000);
--
IF ( v_pDirtyNumeric is null ) THEN
SET v_vCleanNumeric = null;
ELSE
SET v_vCleanNumeric = '';
SET v_vDirtyPos = 1;
SET v_vDirtyLen = LENGTH( v_pDirtyNumeric );
--
WHILE ( v_vDirtyPos <= v_vDirtyLen ) DO
SET v_vDirtyChar = SUBSTR( v_pDirtyNumeric, v_vDirtyPos, 1
);
IF ( v_vDirtyChar IN ( '0', '1', '2', '3', '4', '5', '6',
'7', '8', '9', '.', ' ', '-','+',' ' ) ) THEN
SET v_vCleanNumeric = CONCAT(v_vCleanNumeric,
v_vDirtyChar);
ELSEIF (v_vDirtyChar NOT IN (' ')) THEN
SET v_vCleanNumeric = 'NOT NUMERIC';
RETURN v_vCleanNumeric;
END IF;
SET v_vDirtyPos = v_vDirtyPos + 1;
END WHILE;
--
END IF;
--
RETURN v_vCleanNumeric;
END @

-- Bill Province


DB2 & UDB email list listserv db2-l LazyDBA home page