Joined: 02 Dec 2002 Posts: 620 Topics: 173 Location: Stockholm, Sweden
Posted: Thu May 16, 2024 5:04 am Post subject: Displaying binary data as "readable" such
I'm selecting data from a DB2 table that contains 2 binary fields. These fields (in reality) contain the equivalent of a DL/1 segment, so they can easily contain a mixture of character and COMP-3 fields.
The following is the actual select statement I'm using
Code:
SELECT T6H0.PNR
,hex(SUBSTR(T6H0.DL1_SEGMENT1,01,2)) AS GENNR
,T6H0.NAMN
,T6H0.ADR
,T6H0.POSTNR
,T6H0.ORT
,T6H0.NAMNKOD
,hex(SUBSTR(T6H0.DL1_SEGMENT2,01,1)) AS ROLL
,hex(SUBSTR(T6H0.DL1_SEGMENT2,02,5)) AS DFLDAT
,hex(SUBSTR(T6H0.DL1_SEGMENT2,07,1)) AS KUNDKOD1
,hex(SUBSTR(T6H0.DL1_SEGMENT2,08,1)) AS KUNDKOD2
,hex(SUBSTR(T6H0.DL1_SEGMENT2,09,1)) AS KUADRKOD
,hex(SUBSTR(T6H0.DL1_SEGMENT2,10,5)) AS ANSVDC
,hex(SUBSTR(T6H0.DL1_SEGMENT2,15,1)) AS DCRESERV1
,hex(SUBSTR(T6H0.DL1_SEGMENT2,16,5)) AS TRANSDAT
,objektid as OBJEKTID
,objektbort as OBJBORT
FROM TDRT6H0 T6H0
inner join TDRT6g0 T6g0
on T6H0.CCK_PNR = T6g0.CCK_PNR
WHERE T6H0.CCK_PNR = 96611168177
ORDER BY T6H0.EXTRACT_ORDER ASC
and these are the results
Quote:
0
0005
SIMPSON, MICHAEL
Any st 110
38276
MALERAS
10
40
000000000C
40
40
D6
4040404040
40
020200403C
6158902711100
What I would like to do would be to show the EBCDIC characters as ASCII (?), ie, show a 40 as a "space" and the D6 results as the letter O (as in Oscar).
In addition, it would be nice to show the 020200403C character as the decimal it really is.
I'm assuming I need SOMETHING like CAST or similar, but whatever I try doing doesn't result in what I'm after.
Joined: 26 Nov 2002 Posts: 12368 Topics: 75 Location: San Jose
Posted: Thu May 16, 2024 2:52 pm Post subject:
misi01,
misi01 wrote:
What I would like to do would be to show the EBCDIC characters as ASCII (?), ie, show a 40 as a "space" and the D6 results as the letter O (as in Oscar).
It is not ASCII. X'40' is space in EBCDIC and in ASCII it is X'20'.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum