Need SQL query to convert HEX into CHAR



IBM's flagship relational database management system

Need SQL query to convert HEX into CHAR

Postby nishantsinghal » Sat Feb 16, 2013 1:00 am

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...
nishantsinghal
 
Posts: 10
Joined: Tue Oct 06, 2009 8:09 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Need SQL query to convert HEX into CHAR

 

Re: Need SQL query to convert HEX into CHAR

Postby nishantsinghal » Mon Feb 18, 2013 12:34 pm

Didnt get any response... :(
Please help me out, atleast provide me some suggestions
very strange, nobdy replied on such a huge plateform :(
nishantsinghal
 
Posts: 10
Joined: Tue Oct 06, 2009 8:09 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Need SQL query to convert HEX into CHAR

Postby enrico-sorichetti » Mon Feb 18, 2013 2:21 pm

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
Last edited by enrico-sorichetti on Mon Feb 18, 2013 2:26 pm, edited 1 time in total.
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2644
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 130 times

Re: Need SQL query to convert HEX into CHAR

Postby BillyBoyo » Mon Feb 18, 2013 2:43 pm

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Need SQL query to convert HEX into CHAR

Postby GuyC » Mon Feb 18, 2013 3:58 pm

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
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Need SQL query to convert HEX into CHAR

Postby NicC » Mon Feb 18, 2013 5:21 pm

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.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post