Separating a binary value

[dja1]

I have a column, (say Mixed_Value), in a table, defined as CHAR(17).
It is populated from a dataset defined, (in COBOL), as follows;
03 Group-a.
05 Alpha-Value pic x(13).
05 Num-value pic s9(8) comp.

So, an ASCII / Hex "Dump" of Group-a looks like;
EMPRCENTTERMX

CDDDCCDEECDDE0000
54793553359470001

Question - how, using SQL, can I find the value of the Num-value part of Mixed_Value ?
I have tried various variations of substr(Mixed_value), with no success.

 

[LKBrwnDBA]

Call this function with SUBSTR(mixed_value,14,4):

Create Or Replace Function Comp2Num(P_Comp Raw)
Return Number Is
L_Raw Varchar2(32);
Hex Char(16):='0123456789ABCDEF';
I Pls_Integer;
J Pls_Integer;
N0 Number:=0;
Begin
  L_Raw := Upper(Rawtohex(P_Comp));
  For I In Reverse 1..Length(L_Raw)
  Loop
    J := Instr(Hex, Substr(L_Raw,I,1)) - 1;
    N0 := N0 + (J * 16**(Length(L_Raw)-I));
  End Loop;
  Return N0;
End;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[carp]

If you can split the HEX value off, you can convert it to a number with TO_NUMBER(my_hex_value,'XXXXXXXXXXXXXXXXX').