Page 1 of 1

Need SQL query to convert HEX into CHAR

PostPosted: Sat Feb 16, 2013 1:00 am
by nishantsinghal
Hi Mainframers...

I need a SQL query which can convert hexadecimal value into the character. The sql query should read the hex value and convert it into character.

Input----> D7C1D7E3C3F0F0F1F2F3F5F
Output should be ----> PAPTC0012358

I dnt wanna do any COBOL changes and want SQL query only.

Thanks in advance...

Re: Need SQL query to convert HEX into CHAR

PostPosted: Mon Feb 18, 2013 12:34 pm
by nishantsinghal
Didnt get any response... :(
Please help me out, atleast provide me some suggestions
very strange, nobdy replied on such a huge plateform :(

Re: Need SQL query to convert HEX into CHAR

PostPosted: Mon Feb 18, 2013 2:21 pm
by enrico-sorichetti
do not solicit for answers please ...

remember ... replying on a forum is
on our own time,
free of charge
based on the interest of the question asked
quality of the requirement

or in other less PC wording
when and if we feel like

there is no commitment to answer
there should be no expectation to get a reply

and when You use words like I want
Your benevolence factor will drop dramatically

most probably nobody replied because the requirement is ... at least illogic,
why bother SQL when it can be done more easily
in the way You do not want to

Re: Need SQL query to convert HEX into CHAR

PostPosted: Mon Feb 18, 2013 2:43 pm
by BillyBoyo
I did this in google - convert hex to character sql -, took the first "hit", followed the link. Go for it if that's what you want. Seems pretty dumb requirement, but there we go.

Re: Need SQL query to convert HEX into CHAR

PostPosted: Mon Feb 18, 2013 3:58 pm
by GuyC
this is a (start of a) solution I posted on another forum :
CREATE FUNCTION
 My_HEX2EBCDIC (I_STRING VARCHAR(50))
 RETURNS VARCHAR(50)
 
BEGIN
DECLARE v_hex1 char(1);
DECLARE v_hex2 char(1);
DECLARE v_int INTEGER;
DECLARE v_start INTEGER;
DECLARE v_string varchar(50);

SET v_start = 1;
SET v_string = '';

WHILE v_start < length(I_string) DO
  SET v_hex1 = substr(i_string,v_start,1);
  SET v_hex2 = substr(i_string,v_start+1,1);
  SET v_int = (locate(v_hex1,'0123456789ABCDEF') - 1 ) * 16 + locate(v_hex2,'0123456789ABCDEF') - 1 ;
  set v_string = v_string || EBCDIC_CHR(v_int);
  SET v_Start = v_Start + 2;
END WHILE;
return v_String;
END;


select My_hex2ebcdic('F0F1F2')
, My_hex2ebcdic(HEX('GuyC'))  from sysibm.sysdummy1

Re: Need SQL query to convert HEX into CHAR

PostPosted: Mon Feb 18, 2013 5:21 pm
by NicC
Apart from anything else you posted late on a Friday. Many people do not look at the forums over the weekend and if you are in India a large number of the respondents do not log on until hours after you.