
-- DB2 provides a hex function to convert standard datatypes to hexadecimal, but there is no built-in reverse function.
-- This SQL function provides a character representation of a hexadecimal string. This is particularly useful for querying FOR BIT character columns. 
-- Preparation: first create and populate a translation table - ksh code is shown below.
-- Alex Levy Sustainable Software Ltd. http://www.sustainablesoftware.net
-- April 2005.

--       PREPARATION
-------- #!/bin/ksh
--------. /home/instance/sqllib/db2profile
--------db2 "connect to dbalias"
--------db2 "create table alex.hexchar     \
--------    (hex_chars   char(2) ,         \
--------    display_char char(1) not null) \
--------    in userspace1                  \
--------   partitioning key (display_char) "
    
--------typeset -i i=0; 
--------while (( (i+=1) < 256 ))
--------do
--------    db2 "insert into alex.hexchar \
--------         (display_char)           \
--------	  values(chr($i))               "
--------done

--------db2 "update alex.hexchar set hex_chars = hex(display_char)"
--------db2 "commit" && db2 "terminate"

create function unhex (arg1 varchar(255))
returns varchar(128)                             
specific unhex language sql
deterministic no external action
reads sql data
     
begin atomic
  declare startpos, searchlength, inlength smallint ;
  declare display varchar(128) default '';
  declare a_char char(1) ;
  set startpos=1;
  set searchlength=2;
  set inlength=length(arg1) ;
  
while startpos < inlength do
   set a_char = (select display_char
	               from   alex.hexchar 
	               where  hex_chars=substr(arg1,startpos,searchlength)) ;
   set display = substr(display,1,length(display)) concat a_char ;
   set startpos = startpos + searchlength ;
end while;

return display ;

-- end compound sql
  end  
@

--comment on function unhex is 'Provide the character equivalent of a hexadecimal string, based on the database''''s code page.';
