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;
angoca
commented
10 years ago
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 '';
END@