Home » SQL & PL/SQL » SQL & PL/SQL » ten-digit hexadecimal number (12.1.0.2.0)
ten-digit hexadecimal number [message #683076] Tue, 01 December 2020 01:31 Go to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member
Hello

I would like to ask you to give me an advice.

I need to generate unique ten-digit hexadecimal number.

It looks like this:
00037C534B
00037C55D0
00037C562B

Does anybody know how to do this the best ?

Thanks

Regards
Martin
Re: ten-digit hexadecimal number [message #683078 is a reply to message #683076] Tue, 01 December 2020 04:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select substr(SYS_GUID(),1,10) from dual connect  by level < 10;
SUBSTR(SYS
----------
3214508E95
3F1E5A3B47
4AB8043A78
AA1E91CB0D
69E781CA53
1F1886F54F
0231AF3F07
96CC564EC0
24D7DF2BAE
Re: ten-digit hexadecimal number [message #683079 is a reply to message #683078] Tue, 01 December 2020 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select to_char(dbms_random.value(0,1099511627775),'XXXXXXXXXX') from dual connect  by level < 10;
TO_CHAR(DBM
-----------
 85C6F6BA47
 C56CD89CE7
 5F5C0834E2
 D647A47AF7
 9DE6FCA93B
 B17D150B82
 6D8C29CDFA
 C399A2D124
 7A7BE656D6
Re: ten-digit hexadecimal number [message #683082 is a reply to message #683079] Tue, 01 December 2020 06:08 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3055
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel,

Random != unique (also you need FM modifier otherwise TO_CHAR will produce 11 characters - leading space for plus sign). Chance of duplicate GUID is exteremely low and even though substr 10 out of 16 hex digits from GUID makes that chance a bit higher it is still extermely low but it exists. OP can simply use sequence to get 100% guaranteed uniqueness:

SQL> create sequence seq;

Sequence created.

SQL> select  to_char(seq.nextval,'FM000000000X') hex10
  2    from  dual
  3    connect by level <= 32
  4  /

HEX10
-----------
0000000021
0000000022
0000000023
0000000024
0000000025
0000000026
0000000027
0000000028
0000000029
000000002A
000000002B
000000002C
000000002D
000000002E
000000002F
0000000030
0000000031
0000000032
0000000033
0000000034
0000000035
0000000036
0000000037
0000000038
0000000039
000000003A
000000003B
000000003C
000000003D
000000003E
000000003F
0000000040

32 rows selected.

SQL>

[Updated on: Tue, 01 December 2020 06:12]

Report message to a moderator

Re: ten-digit hexadecimal number [message #683083 is a reply to message #683082] Tue, 01 December 2020 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"Random != unique", sure! as you say, duplicates in random values out of 1 billion (long scale or 1 trillion short scale) values is quite low. Wink


Re: ten-digit hexadecimal number [message #683107 is a reply to message #683076] Thu, 03 December 2020 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A feedback would be welcome here.

Re: ten-digit hexadecimal number [message #683158 is a reply to message #683107] Wed, 09 December 2020 03:28 Go to previous messageGo to next message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member
thanks
Re: ten-digit hexadecimal number [message #683160 is a reply to message #683158] Wed, 09 December 2020 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 67850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is not the feedback I asked.
What solution did you choose, dbms_random or sequence (or something else), and why?

Re: ten-digit hexadecimal number [message #683165 is a reply to message #683160] Wed, 09 December 2020 06:10 Go to previous message
mape
Messages: 280
Registered: July 2006
Location: Slovakia
Senior Member
I used a sequence
Previous Topic: REGEXP_REPLACE
Next Topic: XML into one row
Goto Forum:
  


Current Time: Sun May 16 06:16:38 CDT 2021