Solved

# Increment hexadecimal number by 1 in Oracle

Posted on 2009-04-09
Medium Priority
1,394 Views
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 ?

0
• 4
• 2

LVL 74

Expert Comment

ID: 24109124
I'm not sure what your example is showing,.

that's a difference of 175921860444160
0

LVL 74

Expert Comment

ID: 24109212
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

Author Comment

ID: 24109804
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

LVL 21

Expert Comment

ID: 24110027
To get the result you expected , you shoult ad 10 to that part of the string that seems to be a hex number  !

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>

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

LVL 74

Accepted Solution

sdstuber earned 1000 total points
ID: 24110060
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,
)
|| SUBSTR(RAWTOHEX(role_id), 21))
FROM your_table
where ....
0

LVL 74

Expert Comment

ID: 24110094
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,
)
|| SUBSTR(RAWTOHEX(maxrole), 21)) next_role
FROM (  SELECT job_id, MAX(role_id) maxrole
FROM your_table
GROUP BY job_id)
``````
0

Author Closing Comment

ID: 31568616
Thank you sdstuber. Your solution is what I was looking for.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.