DB2 provides the function CHR to convert an integer value to a character based on the ASCII. If you don't have plain ASCII, you will have to worry about the code page conversion, which can quickly get messy (and is probably one of the reasons why the function is not available generally).
The issue with CHR is that it returns only a single character. So you have to loop over the input string. Another issue is that the output of the HEX function returns, well, hex codes. CHR takes an integer in decimal format. Therefore, you will have to convert hex to decimal numbers. Once you know the pieces, tying all this together is straight-forward:
Code:
DROP FUNCTION unhex@
CREATE FUNCTION unhex(in VARCHAR(100) FOR BIT DATA)
RETURNS VARCHAR(50)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE pos INTEGER DEFAULT 1;
DECLARE hex CHAR(2);
DECLARE ascii INTEGER;
DECLARE result VARCHAR(50) DEFAULT '';
IF in IS NULL THEN
RETURN NULL;
END IF;
WHILE ( pos < LENGTH(in) ) DO
SET hex = UPPER(SUBSTR(in, pos, 2));
SET ascii = CASE SUBSTR(hex, 1, 1)
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'A' THEN 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
ELSE 15
END * 16 +
CASE SUBSTR(hex, 2, 1)
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'A' THEN 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
ELSE 15
END;
SET result = result || CHR(ascii);
SET pos = pos + 2;
END WHILE;
RETURN result;
END@
Some simple test:
Code:
$ db2 "values unhex(hex('123'))"
1
--------------------------------------------------
123
1 record(s) selected.
dBforums
>
Display Hex columns
02-04-08, 16:08
Linear Mode
