• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1349
  • Last Modified:

Increment hexadecimal number by 1 in Oracle

Hello everybody,

I'm writing an Oracle stored procedure and I need to increment a hexadecimal number by 1.

For example,
From  02000014000004630509000300002586
To      02000014000004630509A00300002586

Is there a function / code  to accomplish this ?

Thank you for your help
0
edoc_admin
Asked:
edoc_admin
  • 4
  • 2
1 Solution
 
sdstuberCommented:
I'm not sure what your example is showing,.

that's a difference of 175921860444160
0
 
sdstuberCommented:
in general though, you can turn a hex number into a decimal number and add it then convert back

02000014000004630509000300002586 + 1 =  02000014000004630509000300002587

SELECT TO_CHAR(
           TO_NUMBER('02000014000004630509000300002586', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') + 1,
           0xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
       )
           x
  FROM DUAL
0
 
edoc_adminAuthor Commented:
Hi sdstuber,

Let me give you a specific example. I have a field (ROLE_ID type RAW) and there're 6 ROLE_ID records related to the same JOB_ID:

ROLE_ID                                                             JOB_ID
02000014000004630504000300002586   02000014000004630502000100002586
02000014000004630505000300002586   02000014000004630502000100002586
02000014000004630506000300002586   02000014000004630502000100002586
02000014000004630507000300002586   02000014000004630502000100002586
02000014000004630508000300002586   02000014000004630502000100002586
02000014000004630509000300002586   02000014000004630502000100002586

What I would like to calculate is the next one

ROLE_ID                                                             JOB_ID
0200001400000463050A000300002586   02000014000004630502000100002586

Could this be done ?

Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
flow01Commented:
To get the result you expected , you shoult ad 10 to that part of the string that seems to be a hex number  !
Adding 1 results in 20000140000046305091

 1  SELECT V1HEX
  2  , TO_CHAR(TO_NUMBER(V1HEX,HEXFORMAT)    ) V1
  3  , TO_CHAR(TO_NUMBER(V1HEX,HEXFORMAT) + 1) V1P1
  4  , TO_CHAR((TO_NUMBER(V1HEX,HEXFORMAT) + 1),HEXFORMAT) V1HEXP1
  5  , HEXFORMAT
  6  --, ASCII
  7  FROM
  8  (
  9  SELECT V1 V1HEX, RPAD('X',LENGTH(V1),'X') HEXFORMAT
 10  , CONVERT(V1,'WE8EBCDIC500','US7ASCII') ASCII
 11  FROM
 12  (
 13  select '02000014000004630509000300002586' V1 FROM DUAL
 14  UNION
 15  select '020000140000046305090' V1 FROM DUAL
 16  )
 17* )
SQL> /

V1HEX
--------------------------------
V1
----------------------------------------
V1P1
----------------------------------------
V1HEXP1
----------------------------------------
HEXFORMAT
----------------------------------------
020000140000046305090
151115817523822371819664
151115817523822371819665
  20000140000046305091
XXXXXXXXXXXXXXXXXXXXX

02000014000004630509000300002586
2658457576133102747150782025131107718
2658457576133102747150782025131107719
  2000014000004630509000300002587
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


SQL> ed
Wrote file afiedt.buf

  1  SELECT V1HEX
  2  , TO_CHAR(TO_NUMBER(V1HEX,HEXFORMAT)    ) V1
  3  , TO_CHAR(TO_NUMBER(V1HEX,HEXFORMAT) + 10) V1P1
  4  , TO_CHAR((TO_NUMBER(V1HEX,HEXFORMAT) + 10),HEXFORMAT) V1HEXP1
  5  , HEXFORMAT
  6  --, ASCII
  7  FROM
  8  (
  9  SELECT V1 V1HEX, RPAD('X',LENGTH(V1),'X') HEXFORMAT
 10  , CONVERT(V1,'WE8EBCDIC500','US7ASCII') ASCII
 11  FROM
 12  (
 13  select '02000014000004630509000300002586' V1 FROM DUAL
 14  UNION
 15  select '020000140000046305090' V1 FROM DUAL
 16  )
 17* )
SQL> /

V1HEX
--------------------------------
V1
----------------------------------------
V1P1
----------------------------------------
V1HEXP1
----------------------------------------
HEXFORMAT
----------------------------------------
020000140000046305090
151115817523822371819664
151115817523822371819674
  2000014000004630509A
XXXXXXXXXXXXXXXXXXXXX

02000014000004630509000300002586
2658457576133102747150782025131107718
2658457576133102747150782025131107728
  2000014000004630509000300002590
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


SQL>

  1  select lpad(rownum,2,'0'), to_char(lpad(rownum,2,'0'),'XX') from user_objects
  2* where rownum < 32
SQL> /

LP TO_
-- ---
01   1
02   2
03   3
04   4
05   5
06   6
07   7
08   8
09   9
10   A
11   B
12   C
13   D
14   E
15   F
16  10
17  11
18  12
19  13
20  14
21  15
22  16
23  17
24  18
25  19
26  1A
27  1B
28  1C
29  1D
30  1E
31  1F

31 rows selected.
0
 
sdstuberCommented:
looks like your just adding 1 to the substring of the first 20 hex characters and leaving the characters 21 to the end alone.

SELECT HEXTORAW(TO_CHAR(
                    TO_NUMBER(SUBSTR(RAWTOHEX(role_id), 1, 20), RPAD('x', 20, 'x')) + 1,
                    RPAD('fm0', 22, 'X')
                )
                || SUBSTR(RAWTOHEX(role_id), 21))
  FROM your_table
where ....
0
 
sdstuberCommented:
so, to get the next role_id for each job_id


SELECT job_id,
       HEXTORAW(TO_CHAR(
                    TO_NUMBER(SUBSTR(RAWTOHEX(maxrole), 1, 20), RPAD('x', 20, 'x')) + 1,
                    RPAD('fm0', 22, 'X')
                )
                || SUBSTR(RAWTOHEX(maxrole), 21)) next_role
  FROM (  SELECT job_id, MAX(role_id) maxrole
            FROM your_table
        GROUP BY job_id)

Open in new window

0
 
edoc_adminAuthor Commented:
Thank you sdstuber. Your solution is what I was looking for.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now